レッスン-投稿一覧パソコン仕事５倍塾ポータル

投稿/コメントを表示します。

エクセルマクロ・VBA発展編１

84nomuraさんの投稿

(投稿ID: 5470) 添付ファイルのダウンロード権限がありません

小川さん

いつもお世話になっております。
オートフィルタでの複数条件での絞り込みをしたいと思っており、添付のようなものを作っています。
プロセスとしては、
①まず、データにヘッダがないため、1行挿入
②オートフィルタを作り、4列目を条件で絞り込んで表示させる
というものです。

しかし、エラーにならないですが、想定通りの結果が表示されません。
（どう上手くいかないか、は次のコメントでお伝えします)
アドバイスを頂けますでしょうか。

(添付がサンプルで作ったファイル、以下のコードが入っています)

>>>
Sub hoge() '上手く動かないオートフィルタです。

Dim AitekaisyaR
Dim Aite1
Dim Aite2
Dim Aite3
Dim Aite4
Dim Aite5

AitekaisyaR = 4
Aite1 = 53610
Aite2 = 53220
Aite3 = "ダミー"
Aite4 = "ダミー"
Aite5 = "ダミー"

ThisWorkbook.Worksheets("1").Rows(1).Insert '1行目に行を挿入(オートフィルタのヘッダ用)
Range("A1").CurrentRegion.AutoFilter Field:=AitekaisyaR, Criteria1:=Array(Aite1, Aite2, Aite3, Aite4, Aite5), Operator:=xlFilterValues '相手会社1～5で、フィルタを絞る

End Sub

2023/04/23 23:23

84nomuraさんのコメント

(コメントID: 8136) 添付ファイルのダウンロード権限がありません

小川様

先ほど、うまく表示されないと言ったオートフィルタですが、マクロ実行すると
添付のスクリーンショットのような状態となります。
(=ヘッダ以外すべて非表示となる)

2023/04/23 23:24

小川慶一さんのコメント

(コメントID: 8137)

条件を文字列型の値として渡せば動作しますね。

Sub hoge_ogawa()

    Dim AitekaisyaR
    Dim Aite1
    Dim Aite2
    Dim Aite3
    Dim Aite4
    Dim Aite5
    
    AitekaisyaR = 4
    Aite1 = "53610" '文字列型になおす
    Aite2 = "53220" '文字列型になおす
    Aite3 = "ダミー"
    Aite4 = "ダミー"
    Aite5 = "ダミー"

    ThisWorkbook.Worksheets("1").Rows(1).Insert '1行目に行を挿入(オートフィルタのヘッダ用)
    Range("A1").CurrentRegion.AutoFilter Field:=AitekaisyaR, Criteria1:=Array(Aite1, Aite2, Aite3, Aite4, Aite5), Operator:=xlFilterValues '相手会社1～5で、フィルタを絞る

End Sub

変数の宣言時は、型も指定しましょう。
(発展編1の第1章で扱っている話です)

複雑な条件のときは、別のシートに条件に一致するものを出力するやり方のほうが簡単です。
基礎編の以下の演習を参考にしてモノにしてください。

条件に一致するデータを別表に転記する。AutoFilterより便利な条件抽出マクロ。
https://forum.pc5bai.com/lesson/page/144/

条件に一致するデータを別シートに転記する
https://forum.pc5bai.com/lesson/page/145/

2023/04/25 06:53

84nomuraさんのコメント

(コメントID: 8186) 添付ファイルのダウンロード権限がありません

小川様

いつもお世話になっております。
Autofilterについての悩みの問い合わせをさせていただきたく、ご連絡いたしました。
(以下、添付ファイルのコードです。)

Sub hoge()
ThisWorkbook.Worksheets("Sheet1").Activate
'①フィルタが絞り込まれていれば全選択
If ActiveSheet.FilterMode = True Then
ActiveSheet.ShowAllData
End If
'②フィルタの設定がされてない場合は、フィルタの設定する
If ActiveSheet.AutoFilterMode <> True Then
Range("A1:I1").Select
Selection.AutoFilter
End If

＜最終的なゴール＞
やりたいこと
添付のシート"Sheet1"が、どのようなフィルタ設定になっていても、
最終的に　オートフィルタ設定有り＆、どのフィールド(列)も絞り込まれてない状態にしたい
＜マクロの内容＞
そのため、"Sheet1"の状態が、
パターンA　フィルターの設定がされていない場合は、フィルタを設定する
パターンB　フィルタ設定有だが、どこかの列が絞り込まれてるなら絞り込み解除(フィルタ設定は残す)
パターンC　フィルタ設定有で、かつどの列も絞り込まれていないなら、何もしない
というマクロをくみたい｡

②で、フィルタの設定がされてるかはAutoFilterMode)で調べる⇒　<>Trueなら、フィルタの設定をする
①で、フィルタの設定有＆絞り込まれてるかどうかをFilterModeで調べる⇒　Trueなら、showalldataで絞り込みやめる
としたつもりでした。

＜問題点＞
パターンCの場合、②のところでなぜか(オートフィルタはついているのに)フィルタ解除されてしまうのはなぜでしょうか。

ちなみにここから手作業の話ですが、このブックのSheet1のオートフィルタのかけ方は、1行目A列～I列だけを
選択して、フィルタをかけています。(ふつうは列選択でフィルタをかけることが多いですが、Sheet1が元々？テーブルだった為、
そのやり方でしかオートフィルタをかけられませんでした。)
それが悪さをして、②の式でフィルタ無しという判定を食らっているのでしょうか。

以上、よろしくお願いいたします。

2023/06/19 00:36

84nomuraさんのコメント

(コメントID: 8187) 添付ファイルのダウンロード権限がありません

2023/06/19 00:36

小川慶一さんのコメント

(コメントID: 8188)

こんにちは。

.AutoFilter メソッドの実行で mode を切り替えられるので、以下で十分ではないかと思います。

Sub fuga()
    ThisWorkbook.Worksheets("Sheet1").Activate
    
    ' ActiveSheet.FilterMode = True のときは False にする
    If ActiveSheet.FilterMode Then
        Range("A1").CurrentRegion.AutoFilter
    End If
    Debug.Print ActiveSheet.FilterMode 'always false
    
    '何もフィルターされていないが ActiveSheet.FilterMode = True な状態にする
    Range("A1").CurrentRegion.AutoFilter 'autofilter設定
End Sub

これだと、何か問題あるでしょうか。

2023/06/19 12:10

小川慶一さんのコメント

(コメントID: 8189)

あと、基礎編演習のころからお伝えしていますが、エクセルネイティブなオートフィルターの機能を使ってなんとかしようとするより、基礎編演習で紹介したような別シートの転記のパターンのほうが簡単だし紛れがないし応用が効くので、僕ならオートフィルターがうまくいかないなら(というか、そういうことで悩む前に)その方法での実装を検討します。

2023/06/19 12:12

84nomuraさんのコメント

(コメントID: 8190)

小川さん
ご指摘ありがとうございます。
確かに、別シートに転記するならオートフィルターをVBAで書く必要はありません。

転記元の元資料がフィルタで絞り込まれてる場合は多々あり、そういう場合にそなえてオートフィルタを全選択にする
手順は、どんなマクロでも必須と思いお聞きした次第です。

2023/06/19 19:47

田中宏明さんのコメント

(コメントID: 8191)

84nomuraさん

こんにちは。私は定額コースに参加しているオンライン受講生です。

私も元資料がフィルタで絞り込まれていることの不都合に良く出会います。

少し調べてみましたが、テーブルでフィルタしている場合、以下が正しく
動作しないようです。
[Code]
'フィルタ設定されている場合は、一旦、フィルタ解除
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
End If[/Code]

難易度が高いので、②の必ずオートフィルタ設定有りは、
あきらめたほうがよさそうです。

2023/06/19 21:15

小川慶一さんのコメント

(コメントID: 8192)

そういうことですか。

>転記元の元資料がフィルタで絞り込まれてる場合は多々あり、そういう場合にそなえて

ということでしたら、僕ならますます転記のことを考えますね。
VBAなら、フィルターで非表示になった行の値でも問題なく取得できますので。

「エクセルの機能に頼らないほうがシンプルで使い勝手のよいマクロを作れる」ということは多いです。
オートフィルターなんかはその好例と思っています。

クロス集計表を作るときも、ピボットテーブルをどうにかするマクロを書くよりハナから集計を自分でして出力するコードを書いたほうがメンテナンス性の高いものが簡単にできたりします。

2023/06/19 22:32

小川慶一さんのコメント

(コメントID: 8193)

> テーブルでフィルタしている場合

あとから追加された機能ほどVBAと相性悪くなりますよね。
DPRをしっかり抑えてさえいれば、新機能はほとんどいらないと思います。Rのためのハデな機能は、エクセルが苦手な人向けのものです。

2023/06/19 22:33