EXCELのVBAのある列をRange.Find("文字列").rowで行数を求める処理を作っています。

その文字列は処理対象の最終行をあらわすサインなのですがオートフィルタが設定された場合Findに引っかからなくなるようで困っています。

ActiveSheet.AutoFilterMode = False

でオートフィルタの解除は出来ると思ったら

ActiveSheet.AutoFilterMode = True

とは書けないといわれて困り果てています。
何かうまくオートフィルタを解除・設定(復活)する方法があれば教えてください。
あるいは最終行をもっとうまい方法で汎用性のある形で設定できる方法を教えていただけるのでもよいです。
よろしくお願いします。

回答の条件
  • 1人3回まで
  • 登録:2008/01/12 20:58:04
  • 終了:2008/01/18 11:38:32

回答(5件)

id:SALINGER No.1

SALINGER回答回数3454ベストアンサー獲得回数9692008/01/12 21:37:26

ポイント20pt

AutoFilterは次のマクロで解除できると思います。

Sub test()
    If ActiveSheet.AutoFilterMode Then
        ActiveSheet.Range("A1").AutoFilter
    End If
End Sub
id:matttsu

解除するのは上記の方法でもできたのですが…

2008/01/13 10:53:13
id:devichan No.2

devichan回答回数56ベストアンサー獲得回数42008/01/12 21:42:07

ポイント20pt

私が以前やっていたのですが、行数を以下のように求めていました


・必ず何かデータが入っている場所

 →項番などがある場所

・ヌルもしくは空白をサーチ


これで、最終行を判断していました。

id:matttsu

やはりそういう方法しかないのでしょうか…。空白は既にいくつもあるし項番は行が追加された場合に対応できないので避けたかったんですが。参考にします。ありがとうございます。

2008/01/12 22:43:38
id:Dark1984B No.3

黒ひよこ回答回数17ベストアンサー獲得回数02008/01/13 01:20:35

ポイント20pt

自分の場合は、間に空白セルが入る場合の最終行は、

SpecialCellsで最後のセルから取得していますね。

要するに次の通りです。

数値変数=シート.UsedRange.SpecialCells(xlCellTypeLastCell).Row

id:airplant No.4

airplant回答回数220ベストアンサー獲得回数492008/01/13 15:42:08

ポイント50pt

フィルタ有効状態で、下記のどちらまでを対象にするかで違ってきます。

 A.フィルタして見えている最終行まで

 B.フィルタで見えなくなっていてもデータがあるところまで

B.の場合だけ、フィルタのオン/オフが必要です。

●サンプル

A.の場合

Range("C65535").End(xlUp).Row
'又は、
ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

B.の場合

フィルターのオフはできますが、同じ条件で再度表示する方法はないようです。

再度フィルターをするしか無さそうです。

一度フィルター条件を消してから上記Aと同じ方法で最終行を持ってきます。

復活は条件を保存しておいて、再度条件を指定するしか無いようです。

AutoFilterオブジェクトとAutoFilterメソッドのヘルプをご覧ください。

'解除(条件は消える)
ActiveSheet.AutoFilterMode = False
'又は
Selection.AutoFilter  '設定されていることが分かっている場合

'設定(復活)
Range("A1").AutoFilter
'又は
Selection.AutoFilter
'以前の条件を残したままの解除と復活例(条件はCriteria1のみ、列は10個まで)
'解除
    Dim sSave(10) As Variant
    Dim objfilter, i As Integer
    i = 1
    For Each objfilter In ActiveSheet.AutoFilter.Filters
        If objfilter.On Then
            sSave(i) = objfilter.Criteria1
        End If
        i = i + 1
    Next
    ActiveSheet.AutoFilterMode = False

'設定(復活)selectionのところはRange("A1")でもOK。単一セルがselectされた状態であれば、どこでも関係なし
    Selection.AutoFilter
    For i = 1 To ActiveSheet.AutoFilter.Filters.Count
        If sSave(i) <> "" Then
            Selection.AutoFilter field:=i, Criteria1:=sSave(i)
        End If
    Next
id:matttsu

なんかできそうな感じでかなり期待大で実行してみたんですが

Selection.AutoFilterで

実行時エラー '1004'

Range クラスの AutoFilter メソッドが失敗しました。

が出てしまいますねぇ…。

2008/01/13 16:56:33
id:ardarim No.5

ardarim回答回数897ベストアンサー獲得回数1452008/01/13 23:14:52

ポイント50pt

サンプルです。

Sub test()

    Dim af_range As Range
    Dim af_col As Long
    Dim af_criteria1 As String
    Dim af_criteria2 As String
    Dim af_operator As XlAutoFilterOperator

    Set af = ActiveSheet.AutoFilter

    If af Is Nothing Then
        Exit Sub
    End If

    Set af_range = ActiveSheet.AutoFilter.Range

    For i = 1 To af.Filters.Count
        With af.Filters(i)
            If .On Then
                af_col = i
                af_criteria1 = .Criteria1
                af_operator = .Operator
                If af_operator <> 0 Then
                    af_criteria2 = .Criteria2
                End If
            End If
        End With
    Next i

    ActiveSheet.AutoFilterMode = False

    ' ここでオートフィルター解除後の処理を実行

    ' オートフィルター復元
    af_range.AutoFilter field:=af_col, Criteria1:=af_criteria1
    If af_operator <> 0 Then
        ActiveSheet.AutoFilter.Operator = af_operator
        ActiveSheet.AutoFilter.Criteria2 = af_criteria2
    End If
       
End Sub
id:matttsu

やっぱりダメでした~。

af_range.AutoFilter field:=af_col, Criteria1:=af_criteria1

のところで

Range クラスの AutoFilter メソッドが失敗しました。

ですね。もうオートフィルタで一軒でもフィルタさせているとダメって言う処理に挫折しようかとしてます…。

2008/01/15 20:03:26
  • id:airplant
    >Range クラスの AutoFilter メソッドが失敗しました。
    どの例のAutoFilterでエラーになりましたでしょうか?

    一番下(設定・復活)のものは、AutoFilterがある状態で、一度上のコード(解除)を実行する前提で作っています。
    条件を保存しないで(「ActiveSheet.AutoFilterMode = False」のみ)を行ってから、一番下のコードのみを動かしてもエラーになります。

    SelectionをRange("A1")に変更してみるとどうでしょうか?
  • id:ardarim
    ちなみに最終行であれば私の場合たいてい
    UsedRange.Rows.Count
    で済ませてます。
    1行目が空行とかだと工夫(UsedRange.Row+UsedRange.Rows.Count-1)が必要ですけど通常はこれで事足ります。
  • id:matttsu
    '以前の条件を残したままの解除と復活例(条件はCriteria1のみ、列は10個まで)
    の方で実行しました。当然解除を実行して(そこまでは成功してます。)からのソースです。
    この実行エラーの意味がわからなくて困ってます。
    あと最終行ってわけではないんですよね。列ごとにここが最終行と判定したいという結構面倒なことをしようとしてます。
  • id:airplant
    >あと最終行ってわけではないんですよね。列ごとにここが最終行と判定したいという結構面倒なことをしようとしてます。
    Q1.場合分けのAとBのどちらを行いたいということか教えてください
    Q2.列毎に最終行を別にということは、質問にはありませんでしたが、A列が記載されている最終行、B列が記載されている最終行というように別個に扱いたいということでしょうか?
    質問からは、列ごとということは読み取れませんでしたので、ほかの方々の回答と同様に全体の最終行ということで回答しています。

    >この実行エラーの意味がわからなくて困ってます。
    こちらで動かした限りは、うまくいきます。選択を複数セルで行っている場合や10項目を超えた場合は想定していませんので、適宜変更ください。
    このコメントで最初に書いた質問への回答があれば、もっと要望事項に応じたプログラムが掲載できると思います。
  • id:matttsu
    たびたび回答いただいてありがとうございます。
    Q1は「B.フィルタで見えなくなっていてもデータがあるところまで」です。
    Q2はおっしゃるとおりです。人の列、日付の列などそれぞれ最終行を設定して集計したいなどと思っています。
    質問が明瞭でなかったようで申しわけありませんがよろしくお願いします。
  • id:ardarim
    う~~ん
    私の環境ではきちんと動いているんですけどね。

    原理的にはサンプルに書いたとおり、オートフィルターの範囲(af_range)と現在のフィルター対象列番号(af_col)とフィルター文字列(af_criteria1)を覚えておけば最低限復元できるはずなのですけれど。
    これ以上は実際の環境でデバッグしてみないと難しそうですね。

    ' ここでオートフィルター解除後の処理を実行
    の部分に何も入れないで上のプログラムのままなら動作しないですかね?

    あとはエラーが出た時af_range,af_col,af_criteria1に期待する値が入っているかウォッチしてみるしかないですね。
  • id:airplant
    記述に一部間違いがありました。

    下記のように対象オブジェクトをselectionではなく、オートフィルタしている範囲を記載してください。
    誤:selection
    正:Range(A1:D10)

    なお、ヘッダのところにカーソルがある場合は、selectionでもうまく行きます。

    >人の列、日付の列などそれぞれ最終行を設定して集計したいなどと思っています。
    これは、複雑ですね。要はフィルタを解除して、列ごとに最終行を出すのですね。
    そうすると列ごとに「Range("C65535").End(xlUp).Row」のようにする必要があります(この場合は、cellsで記載したほうが楽)。
    プログラムの変更が面倒と思いますので、autofilterを解除して、今までのfindでもいいかも知れません。

    私のは、条件1のみしか保存していませんので、条件2もある場合は、ardarimさんのマクロを参考にしてopと条件2も保存してください。ただし、ardarimさんの場合は、1件のみのフィルター条件しか復旧されませんので、複数件の場合は私のfor文を参考にしてください。
  • id:matttsu
    すいません。再度頂いたソースは試す時間が無く自動終了になってしまいそうなのでいったん終了します。
    サンプルソースでなんとか対応できそうなのでがんばって見ます。
    ありがとうございました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません