EXCEL Advancedfilter Name(Criteria)の自動作成

質問

重複データから重複のないデータを抜き出すため、下記のようにCriteriaRangeを指定せずに、Advancedfilterを実行していますが、その際にシートの特定のセルにCriteriaという名前(Nameオブジェクト)が自動的に作成され、それが残る現象が生じます。
シートにその名前(Criteria)が残ると、別のSUBで重複データから重複のないデータを抜き出すためAdvancedfilterを実行すると機能しません(表題部だけ抜き出してくる)。
そこで、２点ご教示いただければ幸いです。
１　Criteriaを残さないAdvancedfilterの実行方法
２　残ってしまうCriteriaを削除する方法として、下記のコードを加えていますが、Namesコレクションの特定のName(Criteria)を特定して削除させていますが、この方法は＝を使っていることが、後日のコード解析を分かりづらいものにするため、避けた方がよいと別に指導を受けているため、これ以外の効率的な方法があればご教示願います（例えば、Nameのプロパティを変更する方法による対処方法）。

Criteriaを削除するコード
　　Dim Objname As name
For Each Objname In ActiveWorkbook.Names
If Objname.name = "作業用シート!Criteria" Then
Objname.Delete
End If
Next Objname

Advancedfilterのコード
　　With Worksheets("作業用シート")
M = .Range("B10000").End(xlUp).Row
.Range(.Cells(6, 5), .Cells(M, 5)).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("AA6:AA2000"), Unique:=True
end with

回答No.1

kobouzu_su · ベストアンサー

こんにちは。

（１）削除について

If Objname.name Like "*Criteria" Then
If Instr(Objname.name, "Criteria")>0 Then
If Right(Objname.name,8) = "Criteria" Then

（２）Advancedfileterについて

引数の　CriteriaRange:=""　を付加するのはどうでしょう。

.Range(.Cells(6, 5), .Cells(M, 5)).AdvancedFilter _
　　Action:=xlFilterCopy, _
　　CopyToRange:=.Range("AA6:AA2000"),
　　Unique:=True _
　　CriteriaRange:=""

ただ、（２）のようにCriteriaRange:=""　を付加してやれば、
（１）の削除は必要ないかと思いますが。

外してましたらご容赦願います。
以上です。

お礼

早速のご指導ありがとうございました。
（２）は機能しました。参考書を鵜呑みにするしか能のない私にはコロンブスの卵のような発想です。
今後とも、ご指導よろしくお願いします。

回答No.2

Wendy02

こんにちは。

Criteria は、数少ないExcelの予約語ですから、任意で名前を付けなければ、AdvancedFilter等で、自動的に振られる単語には、ブレはありません。

ですから、

削除するなら、以下のようにすればよいです。
なお、Names のコンテナのクラスは、Application, Workbook, Worksheet とありますが、一応、今回は、Workbook クラスにしておけば、問題はないと思います。

　On Error Resume Next
　ThisWorkbook.Names("Criteria").Delete
　If Err.Number > 0 Then
　　MsgBox "'Criteria' が見つかりません", 32
　End If
　On Error GoTo 0

これは、文字の比較ではありません。ブックが、ThisWorkbook ではないのなら、Workbooks("xxxx").Names("Criteria").Delete にしてください。

>この方法は＝を使っていることが、後日のコード解析を分かりづらいものにするため、避けた方がよいと別に指導を受けているため、これ以外の効率的な方法があればご教示願います

それは、指導に対して、誤解をされているか、もしくは、指導された方の認識度が低いかもしれません。

文字列比較は、VB系では、'=' を使いますが、アルファベットには、大文字・小文字の違いがあり、Binary で比較されるために、必ずしも、文字では一致しないことがあります。そのために、モジュール自体をOption Compare Text モードにして、Like 演算子で比較するか、StrConv で、UpperCase(LowerCase) にして文字を統一して変換するか、StrComp のTextCompare モードにするのです。Instrの場合は、引数を省略しないで、TextCompare モードにします。

ちなみに、私は、StrComp を使っています。ただ、予約語に対して、大文字、小文字の違いを考慮するというのは、本来は、それほど重要なことではありませんね。前述のように、'Criteira'は、予約語ですから、自動で付けられたものに自体には、ブレはありません。

お礼

丁寧なご指導ありがとうございました。
私の理解度が浅いところについて、よく分かる説明で助かりました。
文字列比較も色々あり、奥が深いですね。
予約語という発想は私にはなく、予約語について再確認できました。
今後とも、ご指導のほどよろしくお願いします。