調子よく使用していたコードが、急にエラーに！

質問

Windows XP　Home Edition
Excel　2002

つい最近、ご回答して頂いたコードです。
オートフィルタ（▼）がかかった、直上のセルに色を付けるために使用しておりました。
どの行でも、どこでも実行できておりました。
大変、調子がよく使用していたのですが、
本日、下記のようにエラーとなり、動作しなくなってしまいました。

原因の一つは、
当方が、
EntireRowにてオートフィルタ（▼）をかけた場合に、
１、　256列全てにオートフィルタ（▼）がかかる。
２、　Range("A1").CurrentRegionのようにデータがある列までオートフィルタ（▼）がかかる。
のように、２通りの結果となります。
１の時にエラーとなるようです。

Range("A1").CurrentRegion にてオートフィルタ（▼）をかけた場合は、
下記コードはきちんと動作します。

１となってしまうのは、当方の、ブックに何か原因があるのでしょうか。
１の場合でも動作させることはできますでしょうか。
当方のデータシートは、データがとんでいる所がありますので、
Range("A1").CurrentRegionでうまくオートフィルタ（▼）がかからない場合があります。

下記★箇所がエラーとなります。
一般的ではない質問かと思いますが、
何卒、ご教示お願い致します。

'-------------------------------
実行時エラー'424'
オブジェクトが必要です。

と表示されます。
'-------------------------------
Sub Worksheet_Calculate()
Static rng As Range
　　Dim i As Long
　　Dim j As Long
　　If ActiveSheet.AutoFilterMode Then
　　　　With ActiveSheet.AutoFilter
　　　　　　If .Range.Rows(1).Row = 1 Then 'タイトル行が1行目の場合
　　　　　　　　j = 0
　　　　　　Else
　　　　　　　　j = -1
　　　　　　End If
　　　　　　For i = 1 To .Range.Rows(1).Cells.Count
　　　　　　　　If .Filters(i).On Then
　　　　　　　　　　.Range.Rows(1).Offset(j, i - 1).Interior.ColorIndex = 33
　　　　　　　　Else
　　　　　　　　　　.Range.Rows(1).Offset(j, i - 1).Interior.ColorIndex = xlNone　　'★
　　　　　　　　End If
　　　　　　Next i
　　　　　　Set rng = .Range
　　　　End With
　　Else
　　　　If Not rng Is Nothing Then 'リセット（ただしできないことがある）
　　　　　　rng.Rows(1).Offset(j).Interior.ColorIndex = xlNone
　　　　End If
　　　　Set rng = Nothing
　　End If
End Sub

回答No.2

Wendy02 · ベストアンサー

こんにちは。

これで、終わりにはするつもりでは書いていますが、私が見ている限りは、なんとかサポートは続けていくつもりです。

元のコード自体は、別の人が書けば別ですが、そんなに変えようがないと思います。もともと、AutoFilterの範囲を取るところに関しては、触れていません。その部分に、焦点を与えることを考えてみました。元のコードにエラー処理を入れる方法もありますが、なんとなく、ちぐはぐになってきてしまうような気がしました。

(a) >１、　256列全てにオートフィルタ（▼）がかかる。
の場合と、
If .AutoFilter.Range.Columns.Count = .Columns.Count Then

(b)エラーが起きたときに、オブジェクトを再取得するための、エラー処理対策に飛ぶように、
On Error GoTo ErrHandler ---> ErrHandler: -------> Resume
というスタイルにしてやることを考えました。

しかし、(b)の対策を取るのは、私は今のところ状況が見えていません。

余談になりますが、実際に、私の使っているオートフィルタには、そうした問題が起こらないのは、範囲は半固定式ですが、AutoFilter を設けるにしても、範囲は、Range("A1").CurrentRegion というような暗黙的な範囲を取りません。

たぶん、そのイベント・ドリブン型のマクロコード自体の問題ではなくて、その範囲の取り方の問題だと考えました。
ただし、範囲を取るマクロは、ある程度の人間の判断が必要です。

そこで、(a) だけの対策で、AutoFilter の再取得するところから考えてみました。
誤取得をしないような方策をいろいろ立てたコードですが、果たして、うまく取れるかは分かりません。

'-------------------------------------------
'オートフィルタの範囲を取るマクロ
Sub SetAutoFilter()
　　'マウスカーソルを、データのあるところに置いてください。
　　Dim x As Long
　　Dim y As Long
　　Dim rng As Range
　　'オートフィルタ解除
　　If ActiveSheet.AutoFilterMode Then
　　　　ActiveSheet.AutoFilterMode = False
　　　　'Exit Sub ''オートフィルタのOn/Off のトグルにする場合
　　End If
　　If ActiveCell.Value = "" Then
　　　　MsgBox "ActiveCell にデータがありません。", 48
　　　　Exit Sub
　　End If
　　Set rng = ActiveCell.CurrentRegion
　　With rng
　　　　If Application.CountA(.Cells) < 3 Then
　　　　　　MsgBox "オートフィルタ用のデータとして不足しています。", vbInformation
　　　　　　Exit Sub
　　　　End If
　　End With
　　With rng.Cells(1, 1)
　　　　If rng.Columns.Count > 1 Then
　　　　　　x = .End(xlToRight).Column
　　　　Else
　　　　　　'1列しかない場合
　　　　　　x = .Column
　　　　End If
　　　　y = .End(xlDown).Row
　　　　If x = Rows.Count Then
　　　　　　x = Application.CountA(rng.Rows(1))
　　　　　　If x < 3 Then MsgBox "オートフィルタ用の行数が足りません。", 48: Exit Sub
　　　　End If
　　End With
　　With ActiveSheet　'オートフィルタを作る
　　　　.Range(rng.Cells(1), .Cells(y, x)).AutoFilter
　　End With
End Sub

お礼

こんばんは。
度々のご回答、大変に恐れ入ります。

大変、有りがたく存じております。
＞たぶん、そのイベント・ドリブン型のマクロコード自体の問題ではなくて、その範囲の取り方の問題だと考えました。
＞ただし、範囲を取るマクロは、ある程度の人間の判断が必要です。
非常に遅ればせながらですが、なんとか解かってきました。
自分としては、なんとなく解かっていたつもりなんですが、
どおしても、（自身で荒っぽく使用したりしてるようなので）
自分のブック自体の原因かもとも思っていて、はっきりしませんでしたので、
あえてご質問をさせて頂いた次第でございます。

また、最近では、データがだいぶ蓄積してきまして、操作が重くなってきたりしたりで、
（ブック自体に何か支障が出てきたりしたのではと）
いろいろと少し気になっておりました。

今も、いろいろと範囲の取り方を試しておりましたが、少しずつですが解かってきました。
誠に有難うございました。

回答No.1

imogasi

＞EntireRowにてオートフィルタ（▼）をかけた場合に、
１、　256列全てにオートフィルタ（▼）がかかる。
はどう操作するのですか？読者側で再現してコードをテストしてみたいが。
質問者の場合は、データは、256列全てにデータがありますか。
もしデータが無い列が在る場合、その列まで▼が表示されますか？　
だとしたら不思議。

お礼

ご回答、誠に有難うございました。

補足

早速のご回答、誠に有難うございます。

＞１、　256列全てにオートフィルタ（▼）がかかる。
１、　256列全てにオートフィルタの「▼マーク」が表示される。

＞はどう操作するのですか？
●私の場合、（データが200列ぐらいしかない）シート左端の行番号をクリックしますと、
256列全てにオートフィルタの「▼マーク」が表示されます。

＞質問者の場合は、データは、256列全てにデータがありますか。
いいえ、200列ぐらいまでです。
他のシートは50列とか100列です。
現在まで、256列全てにデータがあるシートは所有しておりません。

＞もしデータが無い列が在る場合、その列まで▼が表示されますか？
はいそうです、上記●印の操作を行いますと、
200列ぐらいまでデータがあるシートの場合、
１のように、256列全てにオートフィルタの「▼マーク」が表示されます。

しかし、他の（データが200列以内しかない）シートによっては、上記●印の操作を行っても、
２のように、データがある列までだけ、オートフィルタ（▼）が表示される時もあります。
（シートによっては、256列全てにオートフィルタの「▼マーク」が表示される時もあります）
これは、以前から、どおしてかなと思ってはおりました。
ひょっとして、「色の塗りつぶし」「列幅の変更」「罫線の編集」等だけでも、「データがある」と認識されるのかなとも思っておりました。

よろしくお願い致します。