Autofilterでエラーがでます

質問

以下の質問の付随質問です。
https://okwave.jp/qa/q10091177.html

以下のコードを考えましたが、エラーがでます。
（オブゼクトは、このプロパティ又はメソッドをサポートしていません。）

'絞り込み
Range("A1").AutoFiler 2, "0"

なぜエラーが出るのでしょうか　？

|[A] |[B]
[1] |取引番号 　　　 |Check
[2] |0039-012582-0002　|=IF(LEFT(A2,12)=LEFT(A3,12),1,0)
[3] |0039-012582-0003　|=IF(LEFT(A3,12)=LEFT(A4,12),1,0)
[4] |0039-012582-0004　|=IF(LEFT(A4,12)=LEFT(A5,12),1,0)
[5] |0039-012582-0005　|=IF(LEFT(A5,12)=LEFT(A6,12),1,0)
[6] |0039-012582-0006　|=IF(LEFT(A6,12)=LEFT(A7,12),1,0)
[7] |0039-013727-0002　|=IF(LEFT(A7,12)=LEFT(A8,12),1,0)
[8] |0039-013727-0003　|=IF(LEFT(A8,12)=LEFT(A9,12),1,0)
[9] |0039-013727-0004　|=IF(LEFT(A9,12)=LEFT(A10,12),1,0)
[10]|0039-019733-0006　|=IF(LEFT(A10,12)=LEFT(A11,12),1,0)
[11]|0039-026459-0005　|=IF(LEFT(A11,12)=LEFT(A12,12),1,0)
[12]|0039-026459-0006　|=IF(LEFT(A12,12)=LEFT(A13,12),1,0)
[13]|0039-026607-0003　|=IF(LEFT(A13,12)=LEFT(A14,12),1,0)

Sub test2()

Sheets("sheet1").Select

Columns("B").Clear

Range("B1") = "Check"

Dim i As Long
Dim lc As Long

'処理行の総数
lc = Cells(Rows.Count, "A").End(xlUp).Row

'昇順でソート
Range("A2:A" & lc).Sort key1:=Range("A2"), order1:=xlAscending

'式の書き込み
Range("B2").Formula = "=IF(LEFT(A2,12)=LEFT(A3,12),1,0)"

'式のコピー
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(i, "B") = Range("B2").FormulaR1C1
Next

'絞り込み
Range("A1").AutoFiler 2, "0"

'シート２の初期化
Sheets("Sheet2").Cells.Clear

'絞り込み結果を別シートへコピペ
Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")

'オートフィルター解除
Range("A1").AutoFilter

End Sub

回答No.2

kkkkkm · ベストアンサー

あと余計なお世話だと思いますが、前回のコード、ループで削除する場合は上から削除すると削除したときに検査対象が飛びますので下から削除しないと結果が思わしくないと思います。(6は11にしました)

For i = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Left(Cells(i, "B"), 11) = Left(Cells(i, "B").Offset(-1, 0), 11) Then
Cells(i, "B").Delete Shift:=xlUp
End If
Next

もしくは、削除したときにiを一つ戻す。実際のループ回数が削除した回数だけ増えます。
データが上っていくので対象のセルもしくは次のセルが空白かどうかの検査も必要になります。

For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
If Left(Cells(i, "B"), 11) = Left(Cells(i, "B").Offset(1, 0), 11) And _
Cells(i, "B").Offset(1, 0) <> "" Then
Cells(i, "B").Offset(1, 0).Delete Shift:=xlUp
i = i - 1
End If
Next

お礼

kkkkkmさん、前回のコードの添削も頂きありがとうございます。

参考に以下に修正しましたが書き出される取引番号が正解と違ってしまいます。

|[A] |[B] |[C]|[D]
[1] |取引番号 |取引番号 | |
[2] |0039-012582-0002|0039-012582-0002| |
[3] |0039-012582-0003|0039-013727-0002| |
[4] |0039-012582-0004|0039-019733-0006| |以下が正解
[5] |0039-012582-0005|0039-026459-0005| |
[6] |0039-012582-0006|0039-026607-0003| |
[7] |0039-013727-0002| | |0039-012582-0006
[8] |0039-013727-0003| | |0039-013727-0004
[9] |0039-013727-0004| | |0039-019733-0006
[10]|0039-019733-0006| | |0039-026459-0006
[11]|0039-026459-0005| | |0039-026607-0003
[12]|0039-026459-0006| | |
[13]|0039-026607-0003| | |

なぜでしょうか　？

Sub test2()

With Sheets("DATA")

.Columns("B").Clear

.Range("A:A").Copy .Range("B1")

.Range("B:B").Sort key1:=Range("B1"), order1:=xlDescending

Dim i As Long

For i = .Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Left(.Cells(i, "B"), 11) = Left(.Cells(i, "B").Offset(-1, 0), 11) Then
.Cells(i, "B").Delete Shift:=xlUp
End If
Next

End With

End Sub

補足

すいません。

Editorでは、上手く表示されているのにシートのレイアウトを書き出した
テキストが上手く列ごとに並ばずにカクカクなってしてしまいました。

回答No.9

kkkkkm

> マクロで処理対象のシートが破損していたようで

原因がわかって良かったです。
しかし、エクセルはやわなのかシートが破損したりブックが破損したりわりとしますね。

回答No.8

kkkkkm

With Sheets("DATA")
だと駄目という事ですね…。
ちょっと分からないので別に新しく質問をしてみてはいかがでしょう。
2013では元のコードでソートできています。
また、今回の質問のソートの部分をWithで囲むとどうなるのかもテストしてみてもいいかもしれません。

お礼

kkkkkmさん、お世話をおかけしていましたが原因が判りました。

マクロで処理対象のシートが破損していたようで
シートを削除して別シートで試してみたらソート処理が出来ました。

セルを下の方から順番に調べて消していく方法も正解が出ました。

協力ありがとうございます。

補足

Office 2021　、Windows　１１でテストしています。

アドバイスを受けて

With　を利用しないコードに以下のように変更してみましたが同じ状況です。

Sub test2()

With Sheets("DATA")

.Columns("B").Clear
.Range("A:A").Copy .Range("B1")

End With

Sheets("DATA").Range("A1").Sort key1:=Sheets("DATA").Range("B2"), order1:=xlAscending
Sheets("DATA").Range("A1").Sort key1:=Sheets("DATA").Range("B2"), order1:=xlDescending

With Sheets("DATA")

Dim i As Long

For i = .Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Left(.Cells(i, "B"), 11) = Left(.Cells(i, "B").Offset(-1, 0), 11) Then
.Cells(i, "B").Delete Shift:=xlUp
End If
Next

End With

End Sub

'-------------------------------------------------------------------

別のアプローチで
以下のようにWith Sheets("test")に変更して試してみました。

結果として問題なく処理されました。

Sub test3()

With Sheets("test")
.Range("A1").Sort key1:=.Range("B2"), order1:=xlAscending
.Range("C3").Sort key1:=.Range("B2"), order1:=xlDescending
End With

End Sub

'-------------------------------------------------

思うにwithの問題では無いようです。

ご指摘のように問題点が変わってしまったので
　もう少し頭を冷やして考えてどうしてもだめなら別の質問として投稿する事にします。

今回も教えて頂き感謝いたします。

(スレは、解決にしないでもう少し残して置きますので何か有りましたら
　アドバイスいただけると幸いです。）

回答No.7

imogasi

#3です。補足有難うございます。
CurrentRegion的に扱ってくれるということかな。
今後この点を思い出して、WEBのVBA記事などに当たって、勉強してみます。
小生はRangeオブジェクトに対するのメソッドのフィルタなので複数列を対象にする場合は、範囲全体を指定するものと思っていたが、そうでもないのかもしれない。
（１）左上隅セル指定をすべき
（２）左上隅セルでもよい（ご指摘のメリットあるため）
（３）その他
どれかを念頭にして勉強します
（１）なら、fieldが２でよい（書き方を認める）のは当然でしょうね。

補足

＞CurrentRegion的に扱ってくれるということかな。

そうだと思います。

空白のセルで囲まれた範囲を読み取り、参照するしているので

以下の結果は、
範囲内のセルを参照するようにすれば結果は同じになるはずです。

Range("A1").Sort key1:=.Range("B2"), order1:=xlAscending
Range("A3").Sort key1:=.Range("B2"), order1:=xlAscending
Range("B1").Sort key1:=.Range("B2"), order1:=xlAscending

回答No.6

kkkkkm

order1:=xlAscending
でしたら以下でいいと思います。

.Range("B2:B100").Sort key1:=.Range("B1"), order1:=xlAscending

Dim i As Long

For i = .Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
If Left(.Cells(i, "B"), 11) = Left(.Cells(i, "B").Offset(-1, 0), 11) Then
.Cells(i, "B").Offset(-1, 0).Delete Shift:=xlUp
End If
Next

お礼

kkkkkmさん、凡ミスが多くてすいません。

＞key1:=Range("B1")
＞は
＞key1:=.Range("B1")

訂正の指摘感謝します。
気をつけて見ているつもりでも中々難しいのですが気にするように努力します。

order1:=xlAscendingの件ですが
(order1:=xlDescendingも含む)

おかしな点に気が付きました。

例えば、以下のコードをステップ実行すると
sortの箇所でB列の順番が変更されるはずなのに全く変化しません。
(A列の並び順をコピペしたままの表示で変化がない）

この為上手く処理されずに最終的に書き出される文字列が正解と違っていると思われます。

A1:B13が一つの表と見てないのかと思い
（CurrentRegionで言うところの空白のセルで囲まれた一つの範囲を読み取りしていない　？）
A13、B13以下の全ての行及びC列の全ての行に
ゴミ（空白など）が紛れていないのかもチェックしましたが痕跡はありませんでした。）

|[A] |[B]
[1] |取引番号 |取引番号
[2] |0039-012582-0002|0039-012582-0002
[3] |0039-012582-0003|0039-012582-0003
[4] |0039-012582-0004|0039-012582-0004
[5] |0039-012582-0005|0039-012582-0005
[6] |0039-012582-0006|0039-012582-0006
[7] |0039-013727-0002|0039-013727-0002
[8] |0039-013727-0003|0039-013727-0003
[9] |0039-013727-0004|0039-013727-0004
[10]|0039-019733-0006|0039-019733-0006
[11]|0039-026459-0005|0039-026459-0005
[12]|0039-026459-0006|0039-026459-0006
[13]|0039-026607-0003|0039-026607-0003

With Sheets("DATA")

.Columns("B").Clear

.Range("A:A").Copy .Range("B1")

.Range("A1").Sort key1:=.Range("B2"), order1:=xlAscending
.Range("A1").Sort key1:=.Range("B2"), order1:=xlDescending

'---------------------------------------------
試しに
以下のコードを試すと、明確にB列でソートされて変化がある

Sub test3()

Sheets("test").Range("A1").Sort key1:=Sheets("test").Range("B2"), order1:=xlAscending
Sheets("test").Range("C3").Sort key1:=Sheets("test").Range("B2"), order1:=xlDescending

End Sub

<変更前のDATA>
|[A]|[B]|[C]
[1] |No1|No2|No3
[2] |あ |a | 23
[3] |え |c | 31
[4] |い |h | 33
[5] |き |b | 32
[6] |う |f | 24
[7] |く |d | 46
[8] |お |i | 45
[9] |け |e | 47
[10]|か |g | 30
[11]|こ |j | 50

<xlAscending>の場合の表示
|[A]|[B]|[C]
[1] |あ |a | 23
[2] |き |b | 32
[3] |え |c | 31
[4] |く |d | 46
[5] |け |e | 47
[6] |う |f | 24
[7] |か |g | 30
[8] |い |h | 33
[9] |お |i | 45
[10]|こ |j | 50
[11]|No1|No2|No3

<xlDescending>の場合の表示
|[A]|[B]|[C]
[1] |No1|No2|No3
[2] |こ |j | 50
[3] |お |i | 45
[4] |い |h | 33
[5] |か |g | 30
[6] |う |f | 24
[7] |け |e | 47
[8] |く |d | 46
[9] |え |c | 31
[10]|き |b | 32
[11]|あ |a | 23

'---------------------------------------------

回答No.5

kkkkkm

order1:=xlAscending
だと、お礼のような結果になります。取引番号が最後に行になりますが。

回答No.4

kkkkkm

> 参考に以下に修正しましたが書き出される取引番号が正解と違ってしまいます。

お礼にあったコードをそのまま実行しましたが、逆順ですが添付画像のように正しい結果になりました。

key1:=Range("B1")
は
key1:=.Range("B1")
に変更しました。DATAシート以外を表示して標準モジュールから実行するとエラーになるので。

回答No.3

imogasi

回答はミススペルということでしょうが、
Range("A1").AutoFiler 2, "0"
のようにA1セルだけを指定するケースは、AutoFilerではないのでは？
Range("A1：A20").AutoFiler Field:=2, Criteria1:="0"
のように複数セル範囲が来るのでは。
また、一列だけを問題にする場合　Field:=2はおかしくない？
ーー
質問するときだけでも、初心者の場合は、コードだけでなく、「自分がやりたかった内容を、文章にして」記して、質問すべきだ。
データ例を丁寧に挙げてくれているのは、結構だと思うが。

補足

＞複数セル範囲が来るのでは。

以下の情報では、

わざわざ複数範囲を指定しなくても良いように記載されて
imogashiさんと見解が違っています。

http://officetanaka.net/excel/vba/tips/tips155.htm

以下、本文記事内の抜粋です。

表全体のアドレスではなく、表内のセルを1つだけ指定することをお勧めします。
もし、対象の表がセルA1から始まっているのでしたら「Range("A1")」と書けばいいです。

手動操作でオートフィルタを設定するとき、毎回いちいち表全体を選択しませんよね。
表内にアクティブセルを移動すれば、Excelが表全体を自動的に認識して、オートフィルタを設定してくれます。
手動操作でそれをやっているのですから、マクロでやって悪いことはありません。
それに、この対象に表全体を指定すると、後で面倒になります。
なぜなら、オートフィルタは"絞り込んで終わり"ではないからです。
オートフィルタは必ず、絞り込んだ後で何かをします。コピーしたり編集したり。
だったら、そこまでを想定して記述するべきです。
この対象には、表の左上であるセルA1(Range("A1"))を指定する方が、後でメリットがあります。

>一列だけを問題にする場合　Field:=2はおかしくない？
Range("A1").AutoFiler 2, "0"

リストの左端から「2」番め列をFiledにするの意味ですが違っていますか　？

回答No.1

kkkkkm

AutoFiler
↓
AutoFilter
じゃないでしょうか

お礼

kkkkkmさん、お世話になります。

あ！、スペルミスをしていたのですね。

autofilerと全て小文字で記載していればスペルミスが分かつていたでしょうが
Autofilerとわざわざ書き出しを大文字で記載してしまっていたので全くミスに気が付きませんでした。

訂正してうまく処理できました。

ありがとうございます。