エクセルVBA AdvancedFilterの範囲

質問

エクセルＶＢＡのAdvancedFilterのついて教えてください

AdvancedFilterは検索条件をCriteriaRangeで指定するかと思うのですが、そのつど変更することは可能なのでしょうか

現在、Ｂ３からＯ３に検索するための「見出し」が入力されています
Ｂ４からＯ４に検索したい文字を入力しCriteriaRange:=Range("B3:O4")としてきたのですが、検索を増やし場合があります
Ｂ５からＯ５にも入力し、Range("B3:O5")とすればいいのでしょうが、そのつど検索範囲を変更するのは大変です
あらかじめRange("B3:O5")としてしまうと、４行目までしか入力しなかった場合に、５行目がＯＲで検索されるので、余白行が検索されることになりすべてのデータを抽出してしまいます

最大でも10条件用意しておけば足りると思うので、Range("B3:O13")としたいです
"空白セルの場合は、上の行をコピーする"と命令させておけば、空白セルがなくなりすべてのデータを抽出してしまうということは回避できるのかな？と考えたのですが、どのように実現するか分かりませんでした
それか、"最終行を認識し、そこまでの範囲とする"とか"余白セルを無視する"ことも可能なのでしょうか

自分が思いついたやり方でなくでも構わないので、検索のつど範囲を変更できる方法があれば教えてください
よろしくお願いします

回答No.5

tsubuyuki

> Ｂ列に限ることなく、「Ｂ３からＯ１３の表の中で、入力されているセルを含む行まで」

なるほど、失礼しました。

だとすると、＃３さんの考え方を拝借して、

　　　　Range("フィルタをかける範囲").AdvancedFilter Action:=xlFilterInPlace, _
　　　　　　　　CriteriaRange:=Intersect(Range("B3").CurrentRegion, Columns("B:O")), _
　　　　　　　　Unique:=False

としてやるのが早そうですね。

１４行目以降にも何らかの（検索キー以外の）データがあるなら、

　　　　Intersect(Range("B3").CurrentRegion, Range("B3:O13"))

としてやっても良さそうです。

あ、言い忘れましたが、各Ｒａｎｇｅ・Ｃｏｌｕｍｎｓには
シート名の指定をお忘れなくなさってくださいね。

＃３さま、ありがとうございます。
私も勉強させていただきました。

お礼

追加の返信ありがとうございます

Ｎｏ３さんの回答にも書きましたが、Ａ４からＡ13まで1、2、3、・・・・、10と数字が振ってあり、教えていただいた２つの方法でやってもうまく抽出されません

sheet2に検索するめの表を作成しており、
Intersect(Worksheets("sheet2").Range("B3").CurrentRegion, ・・・といった感じでシートを指定しています
シートの指定方法が駄目なんですかね・・

回答No.4

tom04

こんにちは！
横からお邪魔します。

とりあえずB～O列の最終行取得だけ・・・

各列（他の列）に14行目以降にデータがない場合は（1行目には何らかのデータが入っているという前提）

Dim endRow As Long
endRow = ActiveSheet.UsedRange.Rows.Count

これでデータが入っている最終行が取得できると思います。

もし他列に14行目以降にデータがあったり、B～O列内で14行目以降にデータがある場合は
Cells(Rows.Count, 1).End(xlUp).Row
のような方法では3～13行目内の最終行取得はできませんので

Dim i As Long, endRow As Long
For i = 13 To 3 Step -1
If WorksheetFunction.CountA(Range(Cells(i, "B"), Cells(i, "O"))) <> 0 Then
endRow = i
Exit For
End If
Next i

のような感じで行えば
最終行は　endRow　に格納されます。

※　AdvancedFilter　の直接の回答ではないので、
この程度でごめんなさいね。m（_ _）m

お礼

回答ありがとうございます

私は応用がきくほどＶＢＡを詳しく分かっていませんが、ヒントになりそうですし、おそらく詳しい方が見たら応用できるんでしょうね。。。

ありがとうございました

回答No.3

n-jun

Ｎｏ．１です。

列も範囲限定して
Intersect(Range("B3").CurrentRegion,Rows("3:13"),Columns("B:O"))
こんな感じでは無理かな？

お礼

追加の返信ありがとうございます

前回のn-junさんの回答を参考に、自分も同じコードを作成しました
Ａ4からＡ13まで1から10の数字が振ってあるので

しかし、すべてのデータを抽出してしまっているので失敗だと思われます
なぜ駄目なのか・・・

試しにＡ4の1だけ残し、Ｂ4からＯ4の4行目のみ入力しなければ成功するので、1から10まで入力することで、ＢからＯの列のみ限定できていないということなのだと思うのですが

回答No.2

tsubuyuki

スタートは３行目である・・と言うのは変わらないのでしょうか。
だとしたら、最終行を可変にしてやればＯＫと言うことでしょう。

なので、私からは「（ある列の）最終行を取得する」と言うやり方を提案します。
まぁ、このＱ＆Ａでも良く見かけるやり方ですが、
応用が利きますから、覚えておいて損はありません。

例えば「Ｂ列（２列目）の最終行を知りたい」時には
　　Cells(Rows.Count, 2).End(xlUp).Row
としてやると取得できます。
つまり・・エクセル的にシートの最終行（B1048576セル、2003以前ならB65535セル）から
「Ｃｔｒｌ＋↑」を押した時に止まる行番号を返してきます。

ちなみに「ある行（例えば３行目）の最終列」を取る場合は
　　Cells(3, Columns.Count).End(xlToLeft).Column
で出来ます。

応用として「Ｂ３セルから行方向に連続してデータがあり、その連続データの最終行」なら、
　　Range("B3").End(xlDown).Row
としてもＯＫです。

これを使って、
　　CriteriaRange:=Range("B3:O" & Cells(Rows.Count, 2).End(xlUp).Row)
あるいは
　　CriteriaRange:=Range("B3:O" & Range("B3").End(xlDown).Row)
　　※Ｂ３・Ｂ４セルにも必ずデータが入っている、と言うのが前提
としてやるといつでも最終行を指定してやることが出来ますね。

以上、参考まで。

お礼

さっそくの回答ありがとうございます

Ｂ３からＯ３まで見出しがあるのですが、４行目以降に入力する場合、それがＢ列からＯ列のどのセルに入力されるか分かりません
たとえば、４行目はＢ列（Ｂ４）に入力し、５行目はＧ列（Ｇ５）に入力することがあります
すると、Ｂ列のみの最終行を調べると４行目になりますが、ほかの行ほかの列にも入力されていることを見逃してしまうことになりませんか

なので、Ｂ列に限ることなく、「Ｂ３からＯ１３の表の中で、入力されているセルを含む行まで」としたかったのですが、言葉足らずですみません

回答No.1

n-jun

データがどんな感じで入っているのかですけど。

１～２行目に何もデータがなければ
Range("B3").CurrentRegion

なにかあるなら
Intersect(Range("B3").CurrentRegion,Rows("3:13"))

などでは無理かな？

お礼

ありがとうございます
Ａ列に数字をあらかじめ振っていたので、教えていただいたとおりではだめかもしれませんが、応用すればできそうです
助かりました