[[20230302174133]]　『範囲について』（けい）　ページの最後に飛ぶ

『範囲について』（けい）

 初心者です。
 範囲指定についてですが
 A列のA1を起点としてA行の最終行まで、A列最終行にある列の範囲指定が難しいです。
 下記の結果はA1:D6を範囲指定とできればよいのですが。。
 範囲はデータによって可変しますが、
 下記の表のように範囲指定したくない部分には必ず空白が1行,1列あります。
 よろしくお願いいたします。

	A	B	C	D	E	F
 1	 	BBB	bbb	B		
 2		a		a		
 3		d	d	d		
 4　	項目	1	2	3		BB
 5		1	2	3　　　　　　　 BB
 6	項目	1	2	3		BB
 7						
 8		AA	AA			
 9		AA		AA		
 10				AA

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

ショートカットならA1セルなど表内を選択した状態で[control+shift+*]
https://www.relief.jp/docs/001316.html

VBAなら

 Range(A1).CurrentRegion
（フォーキー） 2023/03/02(木) 18:39:33

ありがとうございます。

それではA1からF10まで範囲になってしまいます。
上記の表ですと、A1からD6まで範囲指定したいです。

（けい） 2023/03/02(木) 20:40:07

こちらではこの表でA1からD6まで範囲指定できてますが。
E列と7行目になにかスペースとか入力されていませんか？

     |[A] |[B]|[C]|[D]|[E]|[F]
 [1] |    |BBB|bbb|B  |   |   
 [2] |    |a  |   |a  |   |   
 [3] |    |d  |d  |d  |   |   
 [4] |項目|  1|  2|  3|   |BB 
 [5] |    |  1|  2|  3|   |BB 
 [6] |項目|  1|  2|  3|   |BB 
 [7] |    |   |   |   |   |   
 [8] |    |AA |AA |   |   |   
 [9] |    |AA |   |AA |   |   
 [10]|    |   |   |AA |   |   
（フォーキー） 2023/03/02(木) 20:56:55

 すみません。
　E列と7行目にスペースが入っています。
 A列の最終行、そこから右の列の最終列(スペース前)までを範囲指定できればいいのですが。
（けい） 2023/03/02(木) 21:09:31

おそらくエクセルの標準機能では無理だと思います。
一応VBAで作成してみました。
アクティブセルを基準に取得した表範囲内にすべて空白かスペースだけの行、列があれば-1した範囲を返すというものです。
スペースは半角スペースと全角スペースのみという前提です。

もっとスマートなやり方は識者の方をお待ちください。

マクロ名は自由に変えてください。
ショートカットに登録して使用してください。

 Sub test()
     Dim rng As Range, i As Long, j As Long
     Dim cnt As Long, endR As Long, endC As Long
     Dim r, c
     Set rng = ActiveCell.CurrentRegion
     r = rng
     c = WorksheetFunction.Transpose(r)
     On Error GoTo myerror
     For i = UBound(r) To 1 Step -1
         cnt = 0
         For j = UBound(c) To 1 Step -1
             Select Case r(i, j)
                 Case "", " ", "　": cnt = cnt + 1
             End Select
         Next
         If cnt = UBound(c) Then endR = i
     Next

     For i = UBound(c) To 1 Step -1
         cnt = 0
         For j = UBound(r) To 1 Step -1
             Select Case c(i, j)
                 Case "", " ", "　": cnt = cnt + 1
             End Select
         Next
         If cnt = UBound(r) Then endC = i
     Next
     rng.Resize(endR - 1, endC - 1).Select
 myerror:
 End Sub

 ※ 22:45 エラー処理を追加しました。
（フォーキー） 2023/03/02(木) 22:30:05

不躾ながら、少し教えて下さい。
＞　E列と7行目にスペースが入っています。
スペースを入れる理由はどういったことなんでしょうか。
空白になってさえいれば、標準的な方法でお望みのことができるはずなんです。
しかし、条件が満たされない。
で、その理由を知りたくなりました。
どうぞよろしくお願いします。
（abc） 2023/03/03(金) 06:40:09

 （フォーキー）様
 本当にありがとうございました。

 （abc）様
 ありがとうございます。
 依頼された表のためすみません、理由は不明です。
 スペースはなく空拍行、空白列でしたら下記のような表でも範囲指定は可能でしょうか。
 可変で範囲指定できればいいのですが。
 下記の表ですと、A1からD6を範囲指定、A列はA6以降は空白で3行目には空白行があります。
 A1からA列の最終行、その行の空白前までを範囲指定できるとよいのですが。
よろしくお願いいたします。

     |[A] |[B]|[C]|[D]|[E]|[F]
 [1] |    |BBB|bbb|B  |   |   
 [2] |    |a  |   |a  |   |   
 [3] |    |   |   |   
 [4] |項目|  1|  2|  3|   |BB 
 [5] |項  |  1|  2|  3|   |BB 
 [6] |項目|  1|  2|  3|   |BB 
 [7] |    |   |   |   |   |   
 [8] |    |AA |AA |   |   |   
 [9] |    |AA |   |AA |   |   
 [10]|    |   |   |AA |   |   
（けい） 2023/03/03(金) 21:16:34

 >A1からA列の最終行、その行の空白前までを範囲指定できるとよいのですが。
どこを範囲指定したいのかよくわかりません。A1からD6のように、具体的にセル番地で示してください。
提示したコードでは解決しなかったんでしょうか。

最初に聞けばよかったですが、範囲指定は何のために行うんですか？
単純にスペースが含まれるセルを空白とみなして範囲指定したい、ということでしたら、スペースを空白に変換するのが一番早いと思いますし、簡単です。
abcさんのいうとおり、あとは標準機能でできると思います。

別の解決策があるかもしれないので、最終的な目標（やりたいこと）を書いていただけると助かります。
（フォーキー） 2023/03/03(金) 21:32:40

ご提示いただきました件、感動いたしました。

説明不足で本当に申し訳ございませんでした。

先ほど、ご教授いただきましてスペースを空白にすることができました、各シートにはスペースはなくなりました。

範囲指定は、印刷範囲、設定して、フォルダのファイルを一括に処理できればとできればと思っています。
ファイルにはシートが複数あり,表の大きさは変わりますがすべてA列のA1からA列の最終行、その最終行から左の列(空白の前)までの範囲指定です。

何卒よろしくお願い申しいたします。

（けい） 2023/03/03(金) 22:57:58

 こんばんは！
Ctrl+↑とかCtrl+←が使えるといいんですけどね(^^;
Dim v As String
v = "A1:" & Intersect(Range("a" & Rows.Count).End(xlUp).EntireRow, Range("r1").End(xlToLeft).EntireColumn).Address(0, 0)
（SoulMan） 2023/03/03(金) 23:15:19

印刷範囲の設定の話でしたか。
とりあえず、次でどうでしょうか。

 1　A1を起点に、アクティブセル領域を印刷範囲にする場合
 Sub test2()
     Dim i As Long
     For i = 1 To Worksheets.Count
         With Worksheets(i)
             .PageSetup.PrintArea = .Range("A1").CurrentRegion.Address
         End With
     Next
     '全シートを改ページプレビューに切り替え（不要なら削除）
     Worksheets.Select
     ActiveWindow.View = xlPageBreakPreview
     Worksheets(1).Select
 End Sub

 2　行はA列の最終行、列は空白までの範囲、の場合
 Sub test3()
     Dim i As Long, r As Long, c As Long
     For i = 1 To Worksheets.Count
         With Worksheets(i)
             r = .Cells(Rows.Count, "A").End(xlUp).Row
             c = .Range("A1").CurrentRegion.Columns.Count
             .PageSetup.PrintArea = .Range("A1").Resize(r, c).Address
         End With
     Next
     '全シートを改ページプレビューに切り替え（不要なら削除）
     Worksheets.Select
     ActiveWindow.View = xlPageBreakPreview
     Worksheets(1).Select
 End Sub

 >フォルダのファイルを一括に処理

 対象フォルダ内に、このマクロを保存したブックを入れてマクロを実行してください。
 ※ブックが開けなかった場合等のエラー処理は省いてます。

 Sub test4()
     Dim buf As String
     buf = Dir(ThisWorkbook.path & "\*.xls?")
     Do While buf <> ""
         If buf <> ThisWorkbook.Name Then
             Workbooks.Open Filename:=ThisWorkbook.path & "\" & buf

             '改ページのマクロを入れる

             ActiveWorkbook.Save
             ActiveWorkbook.Close
         End If
         buf = Dir()
     Loop
 End Sub

（フォーキー） 2023/03/03(金) 23:40:01

 念には念を入れてこんな書き方が安全かも知れません。

 Sub test()
     Rem   A1から、A列の最終行の右方向の空白直前までで定まる範囲を印刷範囲に指定
     Dim r As Long
     Dim c As Long
     Dim i As Long
     For i = 1 To Worksheets.Count
         With Worksheets(i)
             r = .Cells(Rows.Count, "A").End(xlUp).Row
             If .Cells(r, "B") <> "" Then
                 c = .Cells(r, "A").End(xlToRight).Column
             Else
                 c = 1
             End If
             .PageSetup.PrintArea = .Range("A1").Resize(r, c).Address
         End With
     Next
 End Sub
（abc） 2023/03/04(土) 06:54:37

 >A列のA1からA列の最終行、その最終行から左の列(空白の前)までの範囲指定です。
読み落としてました。
abcさんフォローありがとうございます。

 Sub test5()
     Dim buf As String
     Dim r As Long
     Dim c As Long
     Dim i As Long
     buf = Dir(ThisWorkbook.path & "\*.xls?")
     Do While buf <> ""
         If buf <> ThisWorkbook.Name Then
             Workbooks.Open Filename:=ThisWorkbook.path & "\" & buf

             '開いたブックの全シートに印刷範囲を設定
             For i = 1 To Worksheets.Count
                 With Worksheets(i)
                     r = .Cells(Rows.Count, "A").End(xlUp).Row
                         If .Cells(r, "B") <> "" Then
                         c = .Cells(r, "A").End(xlToRight).Column
                     Else
                         c = 1
                     End If
                     .PageSetup.PrintArea = .Range("A1").Resize(r, c).Address
                 End With
             Next

             ActiveWorkbook.Save
             ActiveWorkbook.Close
         End If
         buf = Dir()
     Loop
 End Sub
（フォーキー） 2023/03/04(土) 07:32:50

 >各シートにはスペースはなくなりました。

 なら、B1セル選択、（何か文字が書いてあるセル）
 Ctrl + Shftt + *　（ノートパソの場合）
 等で、いけますけどね

（普通の子） 2023/03/04(土) 16:13:04

 (フォーキー）様  
（abc）様

 本当にありがとうございます、感謝いたします。
 意図する選択ができました。

 ご質問がございます。
 選択範囲の列が違うファイルの、C列のC1の最終行そこから右に空白前まで範囲指定とするとどのように変更したらよいかわからず、申し訳ございませんが、よろしくお願いいたします。

 For i = 1 To Worksheets.Count
                 With Worksheets(i)
                     r = .Cells(Rows.Count, "A").End(xlUp).Row
                         If .Cells(r, "B") <> "" Then
                         c = .Cells(r, "A").End(xlToRight).Column
                     Else
                         c = 1
                     End If
                     .PageSetup.PrintArea = .Range("A1").Resize(r, c).Address
                 End With
             Next
（けい） 2023/03/04(土) 16:40:42

 こんにちは。  ご研究ご苦労様です。

 本論ですが、えーとですね、アドホックに今度はこういうケースがありまして、ということではなく、
 色々なシートを見たときどうなるのか、ということを教えてもらえませんか？
 複数のファイルを相手にするなら、そうした話をしていただかないと、いつまでも終わりません。

 今度のようにC1を起点にするケースは、どういう条件でC1起点なんですか？
 A列、B列には何も入っていないからですか？
 だったら、D1を起点にするケースなどもあるんですか？
 また、自動でこのいうシートはA列で調べる、こういうシートはC列で調べるという基準がないと
 マクロにできません。
 いやそうでもなくて、とにかくシート毎に決まっているんです、ということならマクロにできませんね。

 そして、ご質問のケースでは、対象セル範囲は、C1:〇〇　なんですか？　それとも、A1:〇〇なんですか？
 まあ、そういったことを色々考えていただいて、
 質問をもう少しまとめるような方向に持っていってもらいたいです。

（abc） 2023/03/04(土) 17:15:36

 修正は、たぶんこんな感じですかね。自信ないですが。

            With Worksheets(i)
                r = .Cells(Rows.Count, "C").End(xlUp).Row
                If .Cells(r, "D") <> "" Then
                    c = .Cells(r, "C").End(xlToRight).Column - 2
                Else
                    c = 1
                End If
                .PageSetup.PrintArea = .Range("C1").Resize(r, c).Address
            End With
（フォーキー） 2023/03/04(土) 17:28:11

 （abc）様
 （フォーキー）様

 本当に申し訳ございません。お詫びいたします。

 確認しましたところフォルダのファイルにすでにご回答いただきました下記の1.と今回の2．がありました。

 1．A列のA1からA列の最終行、その最終行から左の列(空白の前)までの範囲指定
 2．C列のC1からC列の最終行、その最終行から左の列(空白の前)までの範囲指定
　　（下記の表ですとC1からF6まで範囲指定したいです）

 各フォルダの各ファイル内は、1．または 2．でシートに混在はしていませんのでフォルダに合わせて使い分けできればと思いました。
 B列は空白列ですが、A列に数値が入っているシートと入っていないシートが混在していました。
 1をお手本に2に範囲指定しようとしましたが難しくご相談させていただきました。

 上記の　c = .Cells(r, "C").End(xlToRight).Column - 2　は設定はできなくて。。

 何卒よろしくお願いいたします。

    　[A] |[B]|[C]|[D]|[E]|[F] [G]|[H]
 [1]　　  |  　　 |BBB|bbb|B  |   |   
 [2] |   　　 |a  |   |a  |   |   
 [3] |    |   |   |   
 [4] |　1 　　項目|  1|  2|  3|   |BB 
 [5] |　　　　項  |  1|  2|  3|   |BB 
 [6] 　 2　 　項目|  1|  2|  3|   |BB 
 [7] |    |   |   |   |   |   
 [8] |    |　　　　AA |AA |   |   |   
 [9] |    |　　　　AA |   |AA |   |   
 [10]|    |   |   |AA |   | 
（けい） 2023/03/04(土) 19:27:59

 確認します。
 提示されたシートレイアウトの
 A4:C4は結合されているんですか？
 A6:C6も同じ質問です。
 8行目、9行目も正確に記述してもらえますか？
   ("|"というセルの区切りを正確に書いてもらえますか）

（abc） 2023/03/04(土) 20:17:58

こちらでは、提示された以下の表でも（フォーキー） 2023/03/04(土) 17:28:11のコードでC1からF6が選択できましたが。

     |[A]|[B]|[C] |[D]|[E]|[F]|[G]|[H]
 [1] |   |   |    |BBB|bbb|B  |   |   
 [2] |   |   |a   |   |a  |   |   |   
 [3] |   |   |    |   |   |   |   |   
 [4] |  1|   |項目|  1|  2|  3|   |BB 
 [5] |   |   |項  |  1|  2|  3|   |BB 
 [6] |  2|   |項目|  1|  2|  3|   |BB 
 [7] |   |   |    |   |   |   |   |   
 [8] |   |   |    |AA |AA |   |   |   
 [9] |   |   |    |AA |   |AA |   |   
 [10]|   |   |    |AA |   |   |   |

 >上記の　c = .Cells(r, "C").End(xlToRight).Column - 2　は設定はできなくて。。
これは、エラーが出たということでしょうか。

 >B列は空白列ですが、A列に数値が入っているシートと入っていないシートが混在していました。
abcさんも言っていますが、A列を起点にする場合とC列を起点にする場合で条件があるのであれば、それを明確にしていただけると助かります。

ここまで書いて、abcさんの投稿を確認しました。
確かに結合セルっぽい表ですね。
（フォーキー） 2023/03/04(土) 20:34:10

  （abc）様
 （フォーキー）様

　申し訳ございません。

  当方の問題でした。パソコンを立ち上げなおすと解決いたしました。

  ご対応してくださり感動と感謝いたします。

  本当にありがとうございました。

（けい） 2023/03/04(土) 21:29:23

＞パソコンを立ち上げなおすと解決いたしました。
信じられますかー。
返答が面倒くさいので逃げたのではー。
（逃げ道） 2023/03/04(土) 22:17:47

　（逃げ道）様

 そうではありません。
 当方では何度試しても処理ができませんでしたが、
 ご回答に確認できたとのこと、
 　c = .Cells(r, "C").End(xlToRight).Column - 2を　
 再起動して記載し直すと動作し解決いたしました。

（けい） 2023/03/05(日) 07:20:16

コメント返信：

[ 一覧(最新更新順) ]

[[20230302174133]] 『範囲について』（けい） ページの最後に飛ぶ

[[20230302174133]]　『範囲について』（けい）　ページの最後に飛ぶ