シートを初期化する前後でマクロの結果に差がある

Question

以下をマクロで実現しています。
①ExcelBookに格納されたデータをコピー・オブジェクトに格納する
②別ExcelBookに貼り付ける
③年度でフィルタをかける
④表示されているデータを削除する
⑤フィルタを解除する

その後、初期化(貼り付けしたデータを削除)して、再度マクロを実行(①～⑤)すると、
初回と異なる結果となります。

初回結果
■フィルタ前データ件数(iAllCount)：0
■フィルタ後データ件数(iCount)：0
■■フィルタ前データ件数(iAllCount)：705
■■フィルタ後データ件数(iCount)：357
初期化後の結果
■フィルタ前データ件数(iAllCount)：0
■フィルタ後データ件数(iCount)：0
■■フィルタ前データ件数(iAllCount)：348
■■フィルタ後データ件数(iCount)：541

初回結果と初期化後の結果の「■■フィルタ前データ件数(iAllCount)」の差が357で、
初回の「■■フィルタ後データ件数(iCount)」と一致するため、
初期化漏れではないかとデバッグ等しているのですが、原因が特定できません…。

何か解決するためのヒントになるようなことがあればご教授いただけないでしょうか。
よろしくお願いいたします。

Function filterNendo() As Integer
    
    Dim rangeAll    As Range    'フィルタ対象データ
    Dim iAllCount   As Integer  'フィルタ前データ件数(全件)
    Dim iCount      As Integer  'フィルタ後データ件数(削除分)
    Sheet2.Select
    
    MsgBox "■フィルタ前データ件数(iAllCount)：" & iAllCount
    MsgBox "■フィルタ後データ件数(iCount)：" & iCount
    
    'シート1の年度と一致しないデータを表示
    Sheet2.Range("J2").AutoFilter Field:=59, Criteria1:="<>" & Sheet1.Range("C15").Value
    Set rangeAll = Sheet2.AutoFilter.Range
    iAllCount = rangeAll.Rows.Count + 1
    iCount = WorksheetFunction.Subtotal(3, Columns(59)) - 1
    
    MsgBox "■■フィルタ前データ件数(iAllCount)：" & iAllCount
    MsgBox "■■フィルタ後データ件数(iCount)：" & iCount
    
    On Error Resume Next
    Sheet2.Rows("3:" & iAllCount).SpecialCells(xlCellTypeVisible).Delete
    On Error GoTo 0

    If Sheet2.FilterMode = True Then
        Sheet2.ShowAllData
    End If
    
    '廃棄処理
    Set rangeAll = Nothing
    Set AutoFilter = Nothing
    
    filterNendo = iCount

End Function

Answer 1

提示のコード、
多分、コード自体は問題ないだろうと思います。
が、表現が（操作手順が）、回りくどいです。
回りくどい表現なので、紛れ（不具合）が起きやすいのかも知れないので、
少し怖いかなという印象です。

例えば、
＞iCount = WorksheetFunction.Subtotal(3, Columns(59)) - 1
とか、
＞Sheet2.Rows("3:" & iAllCount).SpecialCells(xlCellTypeVisible).Delete
これでも、間違いないのかも知れませんが、、、、

えっと、オートフィルターを使うときの注意点として、
「シートがオートフィルターモードの時は、不可視のセルは操作対象から外れる。」
ということです。
なので、敢えて、
SpecialCells(xlCellTypeVisible).Delete
とSpecialCellsメソッドでセル範囲をさらに指定しなおすみたいなことはしなくていいです。

逆に、抽出件数を数える場合は、

.Columns(1).SpecialCells(xlCellTypeVisible).Count

みたいな感じで、列を限定してからSpecialCellsメソッドで、セルの個数を数えることで、
分かります。

そこで、えっと、、、、
プログラムがすんなり動けば、
提示のコードでいいのだろうと思いますが、
途中でエラーなどで止まった後、
オートフィルターが掛かったままで「初期化した」と思い込んでいた時に、
2回目で結果がおかしいということが起きます。

なので、この関数内で必ず、きっちり、結果を書き込むシートを「初期化」する
ようにしてはいかがでしょうか？

Option Explicit


Function myFilter(ByVal sKeyWord As String, _
                  ByRef rngOld As Range, _
                  ByRef rngNew As Range) As Long
    Dim i As Long

    rngNew.Worksheet.UsedRange.ClearContents
    rngOld.Copy rngNew(1)

    rngNew.AutoFilter Field:=59, Criteria1:="<>" & sKeyWord
    With rngNew.Worksheet.AutoFilter.Range
        i = .Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
        If i > 0 Then .Offset(1).EntireRow.Delete
        .AutoFilter
    End With

    myFilter = i
End Function

Sub test()
    Dim i As Long
    i = myFilter("2010", Worksheets(1).Range("A1").CurrentRegion, Worksheets(2).Range("A1"))
    
    MsgBox i & " 件削除"
End Sub

件数で何かを見つけるというより、
オートフィルターの対象セル範囲が、ちゃんと正しく（＝思い通りに）設定されているかを、
ステップインをしてみながら、シート上の状態を確認しながら、
場合によってはイミディエイトウィンドウやウォッチ式なども利用しながら、
デバッグしてみてください。

最後になりましたが、
あえて、全部をコピーしてから不要な行を削除しなくても、
シート1の表で必要な物を抽出し、
コピペすれば、手順が少なくて済みます。

Function myFilter2(key As String, rngFrom As Range, rngTo) As Long
    Dim i As Long
    
    rngTo.Worksheet.UsedRange.ClearContents
    With rngFrom
        .AutoFilter 59, "<>" & key
        i = .Columns(1).SpecialCells(xlCellTypeVisible).Count
        If i > 1 Then .Copy rngTo
        .AutoFilter
    End With
    myFilter2 = i - 1
End Function

Comments

[sa3sa3sa3]

回答ありがとうございます。
VBA初心者なもので、デバッグが不十分でした…。ステップインで1回目・2回目実施してみたところ、
　Sheet2.Range("J2").AutoFilter Field:=59, Criteria1:="<>" & Sheet1.Range("C15").Value
で2回目が348行目までしかフィルタ範囲になっていないことがわかりました。
(348行目までは年度(BG列)でフィルタリングされています。)
ここを解決できればうまくいきそうな気がします…。

※
コピー元のデータなのですが、別ExcelBookにあります。
データ数が多くレスポンスが気になるので、一旦オブジェクトに保持し、そこから転記しています。
(コピー元のデータは多処理でも使用しているので、編集することができません。)

Answer 2

###質問

VBAのコード見ましたが、filterNendo関数は問題ないと思います。
①～⑤のロジックを１回のトリガで実施するマクロを組まれていると思いますが、
1回目の②終了後と2回目の②終了後で全く同じデータがsheet2に出来上がっているか確認して頂きたいです。（全く同じデータなら、結果は同じになると思います）

以下フロー

1回目実行
①②　sheet2にデータコピー(J2:BP) 705件
【質問１】この時のsheet2の状態は1回目と2回目で同じですか？？（この状態で処理を止めて、確認してほしいです。）
③⑤　フィルタ処理(filterNendo関数)
（１）705件のデータをsheet1の年度情報でフィルタ➡357件表示
（２）表示されている357件を削除する➡0件表示
（３）フィルタを解除する(ShowAllData)➡348件表示（=705-357）
1回目終了

【質問２】終了時に348件データが残りますが、残った348件を手動で消すのでしょうか？？

###補足
ttyp03さんとのやり取りを見させて頂きましたが、Field59はJ列を基準にして59列分だと思います。そのため、Field:=59はBPではないかと思います。sheet2の年度情報はBP列に記載されていると思うのですが．．．

＞＞Field:=59にあたる"BG2"を使用し．．．

以上です。

Comments

[sa3sa3sa3]

回答ありがとうございます。
【質問１】③④⑤の処理(filterNendo)を実行しない場合、1回目と2回目では同じ結果となりました。
【質問２】初期化マクロを作成していて、データがあるセルをClearContentsで初期化しています。手動(Deleteキー)で初期化した場合も、同様の事象(正しく動作しない)が生じました。
【補足】③の処理で止めると、年度(BG列)でフィルタリングされていました。
記載いただいた検証(filterNendoを実行せず比較する、初期化について見直す)で、原因が③④⑤にありそうだと改めて思いました。
filterNendoのメソッドをもう少し解析してみようと思います…。

[KazuSaka]

解決したようでよかったです。
autofileterのfield引数はリストの最初の列をfield:1とカウントすると思いますが．．．
以下のサイトにもそのような記載が．．．
（フィルタの対象となるフィールド番号を整数で指定。表の左端列が「1」
https://www.sejuku.net/blog/38849
まあ大丈夫です。

Answer 3

提示されているコードは③に該当するコードと思われます。
件数に影響がある箇所は以下の行のみです。

Sheet2.Range("J2").AutoFilter Field:=59, Criteria1:="<>" & Sheet1.Range("C15").Value

更に詳細に突き詰めると、動的に影響するのは、以下の2箇所です。

• Range("J2")
• Sheet1.Range("C15").Value

これらは恐らく①②の処理で生成されると思われますので、そのあたりを見てみてはいかがでしょうか。
..

Comments

[sa3sa3sa3]

早速の回答ありがとうございます。
記載いただいた2か所についてですが、
•Range("J2")
　msgboxでデバッグしてみましたが、1回目・2回目とも、同じ結果となりました。
　「Sheet2.Range("J2").AutoFilter」で、フィルタ範囲を指定しているつもりなのですが、
　使用方法が誤っているのでしょうか？
　※Field:=59にあたる"BG2"を使用し、「Sheet2.Range("BG2").AutoFilter」としても
　　結果は変わりませんでした。
•Sheet1.Range("C15").Value
　msgboxでデバッグしてみましたが、1回目・2回目とも、同じ結果となりました。

[ttyp03]

Range("J2")から始まる範囲をフィルタ化するという意味だと思うので、その範囲が違う（行数が違う）んじゃないかなと推測しています。
MsgBoxでデバッグできることではないです。

Sheet1.Range("C15").Value
こちらは問題なさそうですね。

[sa3sa3sa3]

Range("J2") はフィルタする範囲の開始セルで、タイトル行を指定しています。
msgboxで1回目・2回目ともタイトルが取得できていることを確認できたので、問題はないと思っているのですが…。
④で表示されているデータ(タイトル行は除く)が削除でき、見た目上は1回目の処理前と同じ状態であることを確認しているのですが、結果が異なる理由がわかりません…。
もしほかに何かお気付きの点があればご教授お願いいたします。

[ttyp03]

J2はあくまでもフィルタ開始位置であって、そこから下方向にどれだけデータ量があるかで結果が違ってくると思います。
そこに差があるのでは？と思っているのですが。

[sa3sa3sa3]

返信ありがとうございます。
③④⑤の処理(filterNendo)をしない状態で1回目と2回目のデータを比較してみましたが、一致していました…。
filterNendoのメソッドを1文ずつ解析してみようと思います…。

Answer 4

デバッグ(ステップイン)により
Sheet2.Range("J2").AutoFilter Field:=59, Criteria1:="<>" & Sheet1.Range("C15").Value
で2回目が348行目までしかフィルタ範囲になっていないことがわかりました。
前回処理の結果がフィルタ範囲に影響していたようなので、filterNendoメソッドの最後にオートフィルタの解除を入れることで解決しました。

皆様、ありがとうございました！