関数による空白行を除き範囲を選択するマクロ

質問

添付ファイルのシートが同じファイル内にある別シートの値を参照の上、元シートの入力がない場合は、if関数により空白表示する様にしてあります。（関数自体は1万行くらいまで入力すみです。）　このシートの空白表示以外のセル（A6:i22)までを選択するマクロを組みたいのですが普通のrange(A6:i6).End(xlDown).Rowを使うと空白表示行を含めた関数のある最終行を選択してしまいます。　サンプルの（A6:i22)をうまく選択するマクロを教えてください。

回答No.4

kagakusuki

回答No.2、3です。

>私の場合は、6行目から空白表示以外の下の行を選択したかったので間に空白行が存在してもそれも含めての選択で構いません。

という事でしたら、何も難しい事は考えずに、G列に数値が表示されている最終行を、単純にMACH関数を使って求めてしまえば良いと思います。

Sub QNo9237306_関数による空白行を除き範囲を選択するマクロ_別案1()

Const FirstRow = 6 '実際のデータが入力されている最初の行
Const ListColumns = "A:I" '表が存在している列範囲
Const SearchColumn = "G" 'データがある行では必ず数値データか日時データが入力されている列
Dim myRows As Range

With ActiveSheet
On Error GoTo Label_Error
Set myRows = Intersect(.Columns(ListColumns), _
.Range("1:" & WorksheetFunction.Match(9E+307, _
.Range(SearchColumn & FirstRow & ":" & SearchColumn & Rows.Count))) _
.Offset(FirstRow - 1))
myRows.Select
Label_Error:
On Error GoTo 0
End With

End Sub

お礼

こちらも参考にさせて頂きます。
どうもありがとうございます。

回答No.3

kagakusuki

回答No.2です。

　済みません。
　うっかりして、コピー&ペーストを行う際に
End With
を除外してコピーしてしまっておりました。

Sub QNo9237306_関数による空白行を除き範囲を選択するマクロ()

Const FirstRow = 6 '実際のデータが入力されている最初の行
Const ListColumns = "A:I" '表が存在している列範囲
Const SearchColumn = "G" 'データがある行では必ず数値データか日時データが入力されている列
Dim myRows As Range, myRange As Range

With ActiveSheet
Set myRange = Intersect(.Columns(SearchColumn), _
.Range(FirstRow & ":" & .Range(SearchColumn & Rows.Count).End(xlUp).row))
If myRange.row < FirstRow Or WorksheetFunction.Count(myRange) = 0 Then
MsgBox "処理すべきデータが見当たりませんません。" & vbCrLf _
& "マクロを終了します。", vbExclamation, "データ無し"

Else
Set myRows = Intersect(.Columns(ListColumns), myRange _
.SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow)
myRows.Select
End If
End With

End Sub

Sub QNo9237306_関数による空白行を除き範囲を選択するマクロ_簡略版()

Const FirstRow = 6 '実際のデータが入力されている最初の行
Const ListColumns = "A:I" '表が存在している列範囲
Const SearchColumn = "G" 'データがある行では必ず数値データか日時データが入力されている列
Dim myRows As Range, myRange As Range

With ActiveSheet
Set myRange = Intersect(.Columns(SearchColumn), _
.Range(FirstRow & ":" & .Range(SearchColumn & Rows.Count).End(xlUp).row))
If myRange.row >= FirstRow And WorksheetFunction.Count(myRange) > 0 Then
Set myRows = Intersect(.Columns(ListColumns), myRange _
.SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow)
myRows.Select
End If
End With

End Sub

お礼

御回答ありがとうございます。
今回の私の例の場合は、A6:i22は空白でない事が前提ですが、この場合ですと途中で空白が存在した場合は、除いてくれるのですね。　これも今後非常に活用出来ます。参考にさせて頂きます。　どうもありがとうございました。

回答No.2

kagakusuki

確認したいのですが、何らかのデータが入力されている行では必ずG列に日付を示す数値データが表示されているのでしょうか？
　或は、他の列で「何らかのデータが入力されている行」では必ずその列のセルに数値データか日付データが表示される事になっている列はありますか？
　もしその様な列が存在している場合には、以下の様なVBAのマクロを使えば、途中に空欄の行が挟まっている場合でも、空欄の行を全て除外して、A列～I列に何らかのデータが表示されている行のみを選択する事が出来ます。

Sub QNo9237306_関数による空白行を除き範囲を選択するマクロ()

Const FirstRow = 6 '実際のデータが入力されている最初の行
Const ListColumns = "A:I" '表が存在している列範囲
Const SearchColumn = "G" 'データがある行では必ず数値データか日時データが入力されている列
Dim myRows As Range, myRange As Range

With ActiveSheet
Set myRange = Intersect(.Columns(SearchColumn), _
.Range(FirstRow & ":" & .Range(SearchColumn & Rows.Count).End(xlUp).row))
If myRange.row < FirstRow Or WorksheetFunction.Count(myRange) = 0 Then
MsgBox "処理すべきデータが見当たりませんません。" & vbCrLf _
& "マクロを終了します。", vbExclamation, "データ無し"

Else
Set myRows = Intersect(.Columns(ListColumns), myRange _
.SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow)
myRows.Select
End If

End Sub

　尚、上記のVBAでは、もしもG列に日付ないしは数値が表示されているセルが1個も存在していなかった場合には、

「処理すべきデータが見当たりませんません。
　マクロを終了します。」

という表示が表れて、マクロの実行を中断する様になっておりますが、もしその様な表示は必要ないという場合には、以下のように少し簡略化する事も出来ます。

Sub QNo9237306_関数による空白行を除き範囲を選択するマクロ_簡略版()

Const FirstRow = 6 '実際のデータが入力されている最初の行
Const ListColumns = "A:I" '表が存在している列範囲
Const SearchColumn = "G" 'データがある行では必ず数値データか日時データが入力されている列
Dim myRows As Range, myRange As Range

With ActiveSheet
Set myRange = Intersect(.Columns(SearchColumn), _
.Range(FirstRow & ":" & .Range(SearchColumn & Rows.Count).End(xlUp).row))
If myRange.row >= FirstRow And WorksheetFunction.Count(myRange) > 0 Then
Set myRows = Intersect(.Columns(ListColumns), myRange _
.SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow)
myRows.Select
End If

End Sub

回答No.1

keithin

＞サンプルの（A6:i22)をうまく選択する

サンプルではＡ６から暫定最後の２２行まで，「もれなく途中に空白を挟まずに」対象行である事になっていますが，ホントにそれでいいんでしょうか？
同様に例えば「６行目から」対象になってることになっていますが，６行目が""を計算されていて，実は７行目が開始だったという事も無しでしょうか。

また
＞元シートの入力がない場合は、if関数により空白表示する様にしてあります。

これは？ＡからＩ列まで，漏れなく関数が埋まっているという事でいいですね。

更に具体的な関数の内容は，
=IF(他のシートのどこかのセルの条件，""，ナニカの計算式）
のように，""を計算している前提でお話しします。

以上のような前提なら，例えば
sub macro1()
dim c as range
　set c = range("A6:A9999").find(what:="*", lookin:=xlvalues, lookat:=xlwhole, searchdirection:=xlprevious)
　if not c is nothing then
　　range("A6:I" & c.row).select
　else
　　msgbox "ALL ROWs BLANK"
　end if
end sub

ぐらいで十分です。

ちなみにもし実は飛び飛びに空白行が紛れるなら，オートフィルタで絞ってしまって「可視セル」だけ選択するような方法でも出来ますね。

お礼

私の質問の内容が不十分ですみませんでした。
私の場合は、6行目から空白表示以外の下の行を選択したかったので間に空白行が存在してもそれも含めての選択で構いません。　

ご指南頂いたマクロでうまくいきました。
どうもありがとうございました。

補足

追加で質問させて頂きたいのですが、このマクロで選択した範囲を更に印刷範囲として設定したい場合はどの様にすればよろしいでしょうか？