フィルタ条件の解除(単独列)

フィルタ条件を列ごとに解除するコード

'1列目のフィルタ条件を解除
Range("A1").AutoFilter 1

フィルタをかけた列の条件を列ごとに解除するには「AutoFilter」メソッドの条件を省略して実行します

1つ目の引数は指定列の数値指定になります
この数値はワークシートの列数とは一致しませんので注意してください
この点に関しては、以下の記事で解説しています

各列ごとにフィルタ条件を解除する動き
列ごとにフィルタ条件を解除

画像の動きの様にメソッドに指定列数を指定するだけで実行すると、その列のみを解除します

この動きはフィルタがかかっているかいないかは関係なく動作するのですが、逆にフィルタがかかっているものを解除する、という動きにするにはフィルタの状態を判定する必要があります

ただ、すべてをまとめて一括で条件解除をするにはShowAllDataメソッドを使用します
以下の記事で解説しています

フィルタをかける(単独列、単独条件)

最も単純な単独列の単独条件によるフィルタをかけるコード

'表範囲の1列目にフィルタをかける
Range("A1").AutoFilter 1, "=条件"

ワークシートの表範囲にフィルタの条件を指定して抽出するには「AutoFilter」メソッドを使用します

Mac版ではサポートされていないため使用できないようです
以下のページにて記載されています
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.autofilter(外部リンク)

このメソッドでは、Excel2007以降で機能がかなり追加されたため引数による柔軟な抽出がより行いやすくなりました

その中でも基本の単独列に単独の条件を指定する方法がこの例コードになります

メソッドの書式

引数(太字は必須引数)
AutoFilter Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown

今回使用する引数は2つです

「Field」はフィルタをかける表範囲の中での列数を数値で指定します
ワークシートの列数では無い点に注意が必要です

「Criteria1」はフィルタをかける際の条件になります
文字列や数値日付データなどで指定をすることが出来ます
またワイルドカードによる部分一致も可能です

Operator以下は今回は割愛します

実際の動き

フィルタをかけた時のシートデータ範囲の動き
フィルタをかける動き

画像の動きを確認してください
今回は2回フィルタをかけています

1回目が1列目に最初に「001」を含むものを指定しています
ワイルドカードによる指定が行えます
なので、「001-01,001-02,001-03」の3つが抽出されています

2回目が2列目に「対象3」を指定しています
これは1つしかデータは存在しないのでそのデータ行のみが抽出されます

条件に関して、1回目と2回目で見比べてもらうと分かる通り、最初に比較演算子を入れますが、省略も可能です
省略した場合は「=」を指定したときと同じ動きをしていることが確認できます
ですが、あまり良い使い方とはいえないかもしれませんので基本的にはちゃんと記載するようにしてください

また途中で解説したように、指定フィールド数はワークシートの列数とは一致しませんので、画像のような体裁のために空白列・行があるような場合は特に注意が必要です
画像を確認してもらうと分かりますが、1列目で変更されているのはB列なのでワークシート上では2列目になります

1列や2列であれば、簡単に判別できますが、これが数十列・数百列となってくると判別するのは困難になります
操作する表の上・左に空白行がある場合は要注意です

また気づいた方もいるかもしれませんが、このメソッドオブジェクトがほぼ意味はありません
表範囲に無いA1セルに対してメソッドを実行しているのに全く問題ありません
もちろんこんな書き方は推奨しません

このメソッドをマクロの記録によりコード作成すると表範囲が指定されています
これを行う必要はありません、そもそもこのメソッド自体がRangeに対してのメソッドなのも不思議なのですが、フィルタ機能はワークシートにつき1つしか作成されません

つまり、このセルを含むワークシートに対してフィルタを実行しているので問題が発生していないわけです
しかし全くの無意味でもありません、フィルタの矢印を設定する範囲を指定することが出来るからです

なので、それを設定する段階ではない画像のような場面ではまったく問題が起きていないのです
問題ないからそれでいいや、とは言わずにせめて表範囲の始点セルくらいは指定するようにしてください

シートのフィルタ設定を取得する

フィルタの適用状態を取得するプロパティ、使用時に注意点があります

'アクティブシートのフィルタ設定状態を取得
Debug.Print ActiveSheet.AutoFilterMode

シートにフィルタが設定適用されているかどうかを調べるには「AutoFilterMode」プロパティを使用します
このプロパティがTrueを返すと適用されており、Falseが返されると適用されていない状態となります

プロパティで設定状態を取得しているときの動き
プロパティ取得の動き

基本的には、フィルタの適用を解除したい場合に解除する前に適用されているかを調べる時に使用します

ちなみに、このプロパティでは設定を行うことも可能ですがFalseだけです
Trueを設定すると実行時エラーが発生します
Falseを代入するとフィルタの適用を解除できます
なので、こちらで解除を行えば十分に感じるかもしれませんがExcelでよく使用されるテーブル使用時に注意点が出てきます

テーブル使用時の注意点

このプロパティはWorksheetオブジェクトのメンバーです
対してテーブルはListObjectというオブジェクトになります
そのため、このプロパティが無効となってしまいます

テーブル使用時にうまくプロパティが取得できていない時の動き
テーブル時の動き

画像を確認してください
この画像ではコードもデータ内容も全く同じものを使用しています

シートのデータ範囲がテーブルに変換されている点が違います

ですが、こちらではフィルタの矢印が表示されていても、いなくてもFalseが返されています
これがこのプロパティでの大きな注意点です

フィルタをかけるメソッド自体は、テーブルかどうかに関わらず使用できます
なので非適用の状態からフィルタを適用する状態にすることは出来ています

ですが、このプロパティは無効となってしまいます
そのため、上記で解説したFalseを設定することによるフィルタの適用解除も使用できません

フィルタの操作自体はテーブルに関わらないので、問題になることはありませんが
AutoFilterModeプロパティ使用時はテーブルの存在にも注意してください

フィルタを解除する

フィルタを解除するコード、解除時には判定を行います

'フィルタが適用されていれば解除する
If ActiveSheet.AutoFilterMode = True Then
Cells.AutoFilter
Else: End If

ワークシートのフィルタを解除するには「AutoFilter」メソッドを引数を全て省略して使用します

ただこのメソッドはプロパティでは無いため、設定の適用・解除という操作が行えず、実行するたびに適用・解除が切り替わります
そのため、このメソッドを使用して解除する場合は事前にフィルタが適用されているかを調べておく必要があります

これを調べてから実行しないと、解除したいのに元々未適用であれば、適用状態になってしまいます

その適用の状態を取得するのがAutoFilterModeプロパティで、こちらはシートのプロパティになります
このプロパティについては以下の記事で解説しています

フィルタの適用設定に関しては、フィルタを指定する際にもこのメソッドを使用するので、未適用状態でフィルタを実行すれば、フィルタの適用と同時にフィルタもかかるので適用する場合にはあまり適用状態を気にする必要はありません
解除する場合のみ例コードの様に判定を行ってください

ちなみに、フィルタを解除するとかかっているフィルタも全て解除されるので、非表示になっているデータは全て表示されます

指定セル以下の行を全て削除する

指定セル以下の行全てを削除するコード、見出しを除外した範囲全てを消去する際に使用します

'2行目以降を削除
Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

データ集計を行ったり、フィルタデータの貼り付けなどで元々あるデータは削除してデータを貼り付けする処理はよくあります
この際に見出し行は削除したくない場合があります
そんな場合に使用するコードです

このコードを使用すると2行目以降のすべてのセルを削除しますが、1行目はそのままなので見出しを再度作成したりする必要が無くなります

データ範囲のみを選択する場合は以下の記事で解説しています

ですがこの選択範囲はデータ行のみになるので、もし未保存の削除セルがあった場合LastCellなどでうまく最終セルが取得できない場合があったり、コピー元のセル範囲がそういったセルを作成してしまっていたりすることがあるので、なるべく例コードの様にデータ範囲を初期化する場合は全てのセル範囲を指定する方が無難です

コード解説

Range(Range("A2"), Range("A" & Cells.Rows.Count)).EntireRow.Delete

このコードは1行ステートメントですが、複数のプロパティを使用しているのでプロパティごとに分割して解説を行います

Range(Range("A2"), Range("A" & Cells.Rows.Count)).~~.~~

まず最初にRangeオブジェクトを指定しています
Rangeオブジェクトでは引数に2つのセル範囲を指定することが出来ます
引数を2つ指定した場合は、1つ目と2つ目のセル範囲を始点と終点として範囲取得します

~~(Range("A2"), ~~).~~.~~

1つ目のセル範囲がA2セルとなります
このセルが指定範囲の始点セルとなります

つまり、このセルの行数を変更することで見出しの行数に対応することが出来ます
A2をA3とすれば3行目以降のすべてを削除するコードになります

ちなみに、この後のプロパティで行自体を取得しているため、A2をB2などの別の列を指定しても問題はありませんが意味はありません
可読性が低下するだけです

~~(~~, Range("A" & Cells.Rows.Count)).~~.~~

2つめの引数にはワークシートの最下行のセルを指定しています
「Cells.Rows.Count」により最終行数値が取得されます
現在のExcelのシートであれば104万弱ですね
その数値にAを結合することで、A列の最下行セルが指定できます

結果としてこのRangeオブジェクトではA2~A1048576までの範囲選択をしていることになります

~~(~~, ~~).EntireRow.~~

ここでは上記で取得したセル範囲の行を範囲として取得しています
EntireRowプロパティを使用すると、指定されたセル範囲の行を取得します
なので、ここでA列といった制限が無くなり、すべての列を対象とする形になります

A2~A1048576というセル範囲が2~1048576という行数の範囲になります

~~(~~, ~~).~~.Delete

最後にDeleteメソッドを使用して、その範囲を削除します
上記までで指定行範囲を取得できているので、そこに対して削除を実行します

もし、この範囲を別のシートで参照している場合、関数やピボットテーブルなどの利用になりますが、その場合はセル参照がエラー値になってしまいます

その場合はDeleteメソッドではなくClearメソッドを使用することでセルの初期化を行うことが出来ます

なおブックのサイズにもよりますが、こういった削除処理を行った際は処理最後にでもブックの上書き保存を行っておいた方が良いです

なぜなら、削除したセル範囲のLastCellなどはそこでしか更新されないためです
またこれを行うことで、スクロールバーの可動域も新しいデータ範囲に調整されますので、あまりにも削除前とのデータ範囲に差がある場合にはユーザーにとっても意味のある保存と言えます

ただ保存はサーバー上であったり、ブックサイズが大きいと時間がかかってしまい
待ち時間がユーザーにとってのストレスになることもあることは認識しておきましょう

処理を指定行に移動する

GoToステートメントは指定の行から処理を実行できます

'指定箇所に処理を移動する
GoTo 移動

移動:

処理の途中で条件分岐などにより、指定処理を飛ばして後続処理を実行したい場合があります
その時に使用するのが「GoTo」ステートメントを使用します

このステートメントの後にラベル名を指定することで、そのラベル行に処理が移動します

ラベルに関しては「ラベル名」に「:」を結合して指定します
日本語も使用できますので、わかりやすい名前を付けます

処理の実行が移動している状態の動き
コードの動き

実際の動きの確認です
GoToステートメントの時点でラベル行に処理が移動するので、後続の「MsgBox “”」が実行されていません

また、ラベル行は処理ではありませんので処理実行の黄色にはなりません
ラベル行の次の行から処理が実行されます

ちなみに、行ラベルはタブを挿入できません

このステートメントは一見、便利なコードのように感じるかもしれませんが多用は禁物です

中でも、一番注意してほしいのがループ処理に使用する場面です
ループの途中で強制的にループを抜けさせる場合、配列データがロックしたりする場合があります
ループでそういった移動処理はなるべく使用しないような処理に作成出来るように検討してください

また、あまりにも多く使用するとどこからどこに飛ぶか分かりにくくなる可能性があります
ステートメント自体の使用数は多くても構いませんが、ラベル行は限定するようにします

自分がよく使用する場面は、処理途中で条件が整っていないために処理を終了する際に、その判定までに複数の変数を使用していて全てを解放する必要がある場合に、Exitステートメント実行前にそれら全てを解放するのではなく、通常処理での解放の手前にGoToステートメントで移動させて、解放を1か所だけにする
というような使い方をします

ちなみに、このステートメントは行ラベルの位置に移動するものなので、下方向だけでなく上方向に移動させることも出来ます
条件が整ってなければ、再度上に戻って処理を実行するようなことも可能です
ただ無限ループに注意が必要です

CreateObject関数について

CreateObject関数はExcelVBAとは別の機能を利用するために使用する関数です

'Excelの新規作成
Dim zzExApp As Object
Set zzExApp = CreateObject("Excel.Application")
'新規作成したExcelの表示
zzExApp.Visible = True
'新規作成したExcelの終了と変数の開放
zzExApp.Quit
Set zzExApp = Nothing

CreateObject関数は、アプリのインスタンスを作成する関数です
この作成したインスタンスは必ず変数に代入して使用します
その時に使用するのは、基本的にはObject型を指定します

例コードではExcelを追加で新規起動します
コードのあるExcelとは別のアプリとして起動しますので、Excel関係の設定であったりを使用中のExcelとは別に指定することが出来ます

VisibleプロパティにTrueを指定することで、表示されるようになります
これを行わないとバックグランド実行となり、変数を開放してしまったり処理が強制終了するとVBAから終了させることが不可能となり起動したままになってしまいます

何らかのブックを開いた状態で、そうなってしまうとそのブックが別のユーザーが使用中となり編集が出来なくなります

この場合にはタスクマネージャーからプロセスの終了を行う必要があります

そのため、この関数を使用してアプリを使用する場合はおおよそVisibleプロパティを最初に設定して表示状態にしておく方が無難です
Visibleプロパティに関しては以下の記事で解説しています

ここまでの話であれば、それほど使用場面は無さそうな関数と感じるかもしれません
ですがこの関数の最大の利用場面は、Excel以外のアプリを使用できることにあります

ExcelVBAの標準機能で使用できるExcelファイル以外のデータが扱えるのはせいぜいテキストデータ程度です
しかし、この関数を使用すると同じOfficeソフトであるWord・PowerPoint・Outlook・AccessそれぞれのアプリやファイルをExcelVBAから操作することもできます
またInternetExplorerの操作によるWeb操作も可能になります

それ以外にもFileSystemObjectやDictionaryの利用時にも使用します

VBAをやり込んでいけば、必ずVBAだけで実現できない場面に遭遇します
その時にVBAの限界と諦めるか、VBAから操作できるものは全て使い切ると考えるかこの関数が使えるかによると言っても過言ではありません

アプリのインスタンスを作成するというだけの関数ですが、この関数からの広がる世界はExcelVBAの世界を遥かに超えた世界になります

どうせならその世界を経験して欲しいなと思います
出来ることが劇的に増えて、とんでもなくおもろいねん、これが

警告音を鳴らす

Beepステートメントはシステム警告音を鳴らします

'警告音を鳴らす
Beep

警告音を鳴らすには、「Beep」ステートメントを使用します
このコードを実行するとシステム設定された警告音を鳴らすことが出来ます
この音の設定はシステム設定になるのでVBAから任意に変更することは出来ません

あまり落とを鳴らすというような処理は好まれない場面が多いとは思いますが、例えば何らかの資料を見ながら入力処理を行う場合に、画面上で注意表示をしてもユーザーに認知されない可能性があります

またメッセージボックスでも音を鳴らすことが出来ますが、処理が停止するため入力処理の作業がそこで停止してしまいます
メッセージを確認しないと次の入力も行えません

入力値が制限外のデータであれば、それで構わないのですが、例えば数字を入力する際に前回入力値との差が10%以上あったら注意を促したい場合には有効です

その差が織り込み済みであれば、無視して操作を続けられるし、音が鳴ればユーザーは一度は画面を確認しますので入力時点で異常の検知や間違いに気づくことができるかもしれません

何らかの注意を促す場合に最低限で使用するようにしましょう

表示位置を指定セルに移動する

指定セルにShowメソッドを使用すると見える位置に移動できます

'A10000セルを見える位置に移動
Range("A10000").Show

処理を終了させた段階で指定の個所を見える様にしておきたい場合があります
そんなときに使用するのが「Show」メソッドです

このメソッドを使用すると、指定したセルがウィンドウ内に収まるように自動的にスクロールされます
また、その際に指定したセルは選択状態にはなりません

選択セルは変更したくないけど、画面を移動させたい場合に有効です

ただ、画面内のどこかまでが指定できません
しかも指定セルが画面内にあれば移動しない場合もありますので少し注意が必要です

コードの動き

メソッドの動きの確認
コード実行時の動き

実際にコードを実行してみると、動きがわかりやすいです

画像の動きを確認してください
A10000セルを表示状態にすると、真ん中に表示されるように画面がスクロールされています
そしてその際、選択セルがA1のままで選択範囲が移動していないことも確認できます

そして、このメソッドの最大の注意点が、画面更新を抑止している時には無効になる点です
画面更新を抑止した状態でA1セルを対象に実行しても変化がありません
抑止を解除してから、再度実行するとちゃんと画面が移動します

処理後に画面を移動させたい場合は多いので、あまり問題にならないとは思いますが画面更新を抑止する処理を作成した場合は解除してから実行するようにしてください

ただ、解説したように画面のどこに指定セルが表示されるか分からないので、どうしても指定セルを指定場所に表示させたい場合はApplication.Gotoメソッドを使用してください
こちらのメソッドであれば左上限定ですが、セルの表示位置を指定できます
あとはOffsetプロパティやスクロールさせれば思い通りの表示状態を作成することができます
Application.Gotoメソッドについては以下の記事で解説しています

シートの内部名を取得する

シートの内部名を取得できるCodeNameプロパティについて

'シートの内部名を取得
Worksheets("Sheet1").CodeName

シートを操作する際に使用する、「Worksheets(“Sheet1”)」というオブジェクトはシート名を名称で直接指定しています

これとは別に、内部名としてシートごとにオブジェクト名が存在します
こちらもシート名同様に同じ名前を付けることが出来ないためユニーク名になります
その内部名を取得するのが「CodeName」プロパティです

この内部名を使用することにより、シート名を変更しても処理に影響が出なくなるため、状況によってはシート名より有効に使用できます

内部名とはプロパティでいうオブジェクト名のことになる
プロパティウィンドウ

今回のプロパティは、赤枠内の(オブジェクト名)という部分の文字列です
通常初期値はシート名と同じようにつけられています

またその下のほうにあるNameプロパティはシートのタブに表示されている文字列になります
通常、シート名を変更するとこちらが変更されます

シート名自体も同じ名前を付けることが出来ないので、オブジェクト名を使ったほうがいい場面の方が多いということはないと思います
状況によって使い分けることが重要です

また、この内部名をオブジェクトとして使用することが可能です

シート名を取得するときの動き
シート名の取得

この画像のコードでは、NameプロパティとCodeNameプロパティの両方を使用して、それぞれの名称文字列を取得しています

やっていることは同じですが、指定の仕方が違っています

オブジェクト名の方が、コードを比較的少なくすることが出来ると思います
またシート名をユーザーに変更されても問題が発生しないのは大きな利点です

ただ、Nameプロパティを使用しない場合、シートをコピー作成して同じシート名にして既存のシートを削除する、というようなことをされると使用できなくなります
また、何らかの理由でCodeNameプロパティに相違が出た際にユーザーが簡単に修正することが出来なくなります

シート名は「~~」にしてください、というようなメッセージでシート名を統一させることは出来ても、内部名を編集してもらうのはVBAを知らない人からすれば困難です

なので、シート名の変更にも対応できて、コードを短くすることもできる、この素晴らしいプロパティは状況によってはユーザーに迷惑になる可能性がありますので注意が必要です

やはり、一番相互にいいのはNameプロパティを使用して、それを最初にオブジェクト変数に代入しておくことかもしれません
そうしておけば、もしシート名が変更になっても1か所の修正で済みます