[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『オートフィルタ』（やったみたい）
1 2
1 a ●●町1丁目
2 b ●●町2丁目
3 c ●●町1丁目
4 d ●●町3丁目
. . ．
. . ．
というデータがあります。
この中で1丁目のデータを抽出し、他は削除したいと思っています。
一つのシートでしたらオートフィルタを使ってできますが、
50シートあるものを一括で処理したいと思っています。
試しに自分でマクロを記録して作ったんですが、それにすべてのシートで同じ処理をす
るようにしてみたのですが、シートごとに削除する行が違うのでうまくいきませんでし
た。
削除する行がシートごとに違うのですが、うまく処理する方法はあるでしょうか？
色々やり方はありますが、オートフィルタやフィルタオプションなど・・・ 私の場合は普段はフィルタを設定するのが面倒なので ColumnDifferencesを使う方法を良く使います。
それを使った該当行以外を非表示にするサンプルコードです。
Sub Test()
Dim ws As Worksheet, r As Range
For Each ws In ThisWorkbook.Worksheets
With ws.Columns("B")
Set r = .Find(What:="*1丁目*", After:=.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, MatchByte:=False, SearchFormat:=False)
If Not r Is Nothing Then .ColumnDifferences(r).EntireRow.Hidden = True
End With
Next ws
End Sub
（ｍｏｍｏ）
ありがとうございます！ うまくいきました。
いろいろあるんですね・・。奥が深い・・。
(やったみたい）
こんばんわ。
うまくいったかと思ったらつまづきました・・。
1 2
ひと組目
名前 住所
1 a ●●町1丁目
2 b ●●町2丁目
3 c ●●町1丁目
4 d ●●町3丁目
. . ．
. . ．
といった風に3行目から始まるので、
cell(3,1)
としました。
しかし、やはりほしいデータ以外は削除したいと思い、hiddenをclearにしたら1行目2行目も消えてしまいました。
clearを使わず、1行目2行目を残しつつ、行を削除できる方法はあるでしょうか？
(やったみたい）
出張で返事が遅くなりました。 こんな感じでしょうか？
Sub Test()
Dim ws As Worksheet, r As Range
For Each ws In ThisWorkbook.Worksheets
With ws.Columns("B")
Set r = .Find(What:="*1丁目*", After:=.Cells(1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, MatchByte:=False, SearchFormat:=False)
If Not r Is Nothing Then _
Application.Intersect(.Rows("3:" & .Rows.Count), _
.ColumnDifferences(r)).EntireRow.Delete Shift:=xlUp
End With
Next ws
End Sub
（ｍｏｍｏ）
ありがとうございます。 休みに入ったので会社で実行できるかはわかりませんが、家で似たデータを作ってやってみたらうまくいきました。 親切に教えていただき、ありがとうございました！
(やったみたい）
何回もすいません。 会社でためすと、1行1列目のセルのひと組目、が消えてしましました。 また、＊1丁目＊としているのですが、1丁目のたとえば、1番地は表示されるのに2番地目以降は削除されてしまいました。 理解がたなりくて申し訳ないのですが、マクロ用のエクセルファイルを作って、対象のエクセルでマクロを実行したのに、何も起こらないのは、”ThisWorkbook.Worksheets”となっているからでしょうか？
（やったみたい）
＞試しに自分でマクロを記録して作ったんですが って事でしたが、どう言ったコードを作ったのですか？
(HANA)
”試しに～”の下りは勘違いでした。もともと何も入っていませんでした。 自分で作ったのは2番目に書いた通りで、今はmomo様に教えていただいたコードを入力しました。 どうしても、＊1丁目＊としているのに、一番最初に記載されている、例えばですが、 名前 住所 1 a ●●町1丁目3番地 2 b ●●町2丁目1番地 3 c ●●町1丁目5番地 4 d ●●町1丁目3番地 . . ． . . ．
となっていると、結果は 1 a ●●町1丁目3番地 4 d ●●町1丁目3番地 となって、３番地以降の1丁目のデータが消えてしましました。
(やったみたい）
ColumnDifferences の EntireRow を Delete ですからね。。。
＞”試しに～”の下りは勘違いでした。 って事は、ご自身で作って居られない って事ですか？
まずはマクロの記録をやってみられては？ 基本的には↓の手順で出来ると思います。 １．２行目を挿入 ２．A2セルを選んでオートフィルタを設定 ３．B2セルのフィルタで「1丁目を含まない」を抽出 ４．範囲を選択して行削除 ５．オートフィルタを解除 ６．２行目を削除
細かい修正やループ処理の追加など 色々追加が必要ですが。
出来たコードをこちらに載せてみて下さい。 また、コード実行前にオートフィルタが設定されている可能性が 有るかどうかも教えて下さい。
(HANA)
おっと。。。。 １の手順で2行目を挿入したので
２．A2セルを選んでオートフィルタを設定 ↓ A3セル
３．B2セルのフィルタで「1丁目を含まない」を抽出 ↓ B3セル
ですね。
失礼しました。
(HANA)
はじめは自分でオートフィルタのマクロを記録してやったんです、すべてのシートにその処理をどのようにするかわからなくて・・・。 vbaの本を見て、「すべてのシートに同じ処理をする」というコードを本をみて書き込んだのですがうまくいかなくて・・・。 Sub () Dim sh As Worksheet
For Each sh In Worksheets ここにオートフィルタの内容
Next sh
End Sub
といったことをしたのですが・・・。
オートフィルタ自体が自分で記録させたものだったのでうまくいくはずはなく（シートごとに削除する行が違うため）・・オートフィルタのマクロコードを本を見てやっては見たのですが、エラーになりました。本は会社にあるもので今、どのようなコードでだめなのか記入できないのですが・・・。 なんせ、超初心者なのでお恥ずかしい限りです。
(やったみたい）
マクロの記録だけで出来る事は限られてきます。 しかし、出来たコードは マクロを作る際の 参考にすることが出来ます。
私は ＞基本的には↓の手順で出来ると思います。 ＞出来たコードをこちらに載せてみて下さい。 と書いています。
(HANA)
会社のデータのようなものでしてみましたが・・・。 Sub Macro1() ' ' Macro1 Macro '
'
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A3").Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$B$11").AutoFilter Field:=2, Criteria1:="=*1丁目*", _
Operator:=xlAnd
End Sub
1行挿入するのはなぜなんでしょうか？ すいません。これでいいのでしょうか・・・？
(やったみたい）
＞これでいいのでしょうか・・・？ 違うと思いますが・・・。
＞ActiveSheet.Range("$A$3:$B$11").AutoFilter
なんて記録には成りませんよね？
「Selection.AutoFilter」
の様に記録されると思いますが。。。
念のため、エクセルのバージョンを教えて下さい。
それから、1丁目以外を削除したいなら
抽出して表示するのは「1丁目を含まない」ですね。
＞３．B2セルのフィルタで「1丁目を含まない」を抽出
~~~~~~~~~~~~~~~
そして、４番以降の記録が無い様ですが？
＞1行挿入するのはなぜなんでしょうか？ 挿入した場合と、していない場合では結果が違うでしょう？
(HANA)
すいません。おっしゃるとおりでした。 ひと組目 名前 住所 a ●●1丁目1番地 b ●●1丁目2番地 c ●●2丁目5番地 d ●●1丁目6番地 e ●●3丁目7番地 f ●●1丁目8番地 g ●●4丁目8番地 というので試しに記録しました。 エクセルは２００７です。 Sub Macro4() ' ' Macro4 Macro '
'
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A3").Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$B$10").AutoFilter Field:=2, Criteria1:="<>*1丁目*", _
Operator:=xlAnd
Rows("6:10").Select
Selection.Delete Shift:=xlUp
Range("A15").Select
ActiveSheet.Range("$A$3:$B$7").AutoFilter Field:=2
Rows("2:2").Select
Selection.Delete Shift:=xlUp
End Sub
となりました。
もう一回
ひと組目
名前 住所
a ●●1丁目1番地
b ●●2丁目2番地
c ●●2丁目3番地
d ●●3丁目4番地
e ●●1丁目5番地
f ●●1丁目6番地
g ●●1丁目7番地
h ●●1丁目8番地
で試すと、
ひと組目
名前 住所
a ●●1丁目1番地
b ●●2丁目2番地
e ●●1丁目5番地
f ●●1丁目6番地
g ●●1丁目7番地
h ●●1丁目8番地
となり、2丁目の結果も残りました。
(やったみたい）
確かに 2007 であれば
＞ActiveSheet.Range("$A$3:$B$10").AutoFilter
と記録されますね。
さて、記録に関してはやはりまだ違う様です。 ＞＞５．オートフィルタを解除 オートフィルタを解除 です。 B列の「1町目を含まない」を「全て表示」にするのではなく。
そこで、 ＞となり、2丁目の結果も残りました。 の部分もこの様にならないよう少し手順を変えてみます。
もう一度記録に取ってもらえますか？ １．２行目を挿入 ２．A3セルを選んでオートフィルタを設定 ３．4行目を選択・・・・・・・・・★ ４．Shift + ↓ を同時に押す・・・★ ５．B3セルのフィルタで「1丁目を含まない」を抽出 ６．右クリック→削除(D)・・・・・★ ７．オートフィルタを解除 ８．２行目を削除
★の行が、主に変更した部分です。 これで、ループ処理以外のコードのほとんどが 完成した事になります。
(HANA)
これでいいのでしょうか。 試しに同じようにやってみたら、2丁目も削除されました！ Sub Macro5() ' ' Macro5 Macro '
'
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A3").Select
Selection.AutoFilter
Rows("4:10").Select
ActiveSheet.Range("$A$3:$B$10").AutoFilter Field:=2, Criteria1:="<>*1丁目*", _
Operator:=xlAnd
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$3:$B$7").AutoFilter Field:=2
Range("B1:G5").Select
Range("B5").Activate
Selection.AutoFilter
Rows("2:2").Select
Selection.Delete Shift:=xlUp
End Sub
となりました。
（やったみたい）
えっと。。。済みません。 手順を書き間違えました。 ４．Shift + ↓ を同時に押す・・・★
４．Ctrl + Shift + ↓ を同時に押す でした。
すると
＞Rows("4:10").Select
は
Rows("4:4").Select
Range(Selection, Selection.End(xlDown)).Select
になります。
それから
＞ActiveSheet.Range("$A$3:$B$7").AutoFilter Field:=2
「全て表示」とかやってるように思いますが。。。。
これも削除したらすぐに「フィルタ解除」すると
↓が記録されます。
Sub 記録マクロ()
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A3").Select
Selection.AutoFilter
Rows("4:4").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range("$A$3:$B$10").AutoFilter Field:=2, Criteria1:="<>*1丁目*", _
Operator:=xlAnd
Selection.Delete Shift:=xlUp
Selection.AutoFilter
Rows("2:2").Select
Selection.Delete Shift:=xlUp
End Sub
ここから、 Selection ってどこの事？ 等に注意しながら不要部分を削除すると
↓スリムになります。
Sub セレクトしない()
Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A3").AutoFilter Field:=2, Criteria1:="<>*1丁目*", Operator:=xlAnd
Range(Rows("4:4"), Rows("4:4").End(xlDown)).Delete Shift:=xlUp
Range("A3").AutoFilter
Rows("2:2").Delete Shift:=xlUp
End Sub
このコードを全シートで実行するように ループ処理を加えます。
↓全シートをループ。
Sub yattamitai()
Dim sh As Worksheet
For Each sh In Worksheets
With sh
.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Range("A3").AutoFilter Field:=2, Criteria1:="<>*1丁目*", Operator:=xlAnd
.Range(.Rows("4:4"), .Rows("4:4").End(xlDown)).Delete Shift:=xlUp
.Range("A3").AutoFilter
.Rows("2:2").Delete Shift:=xlUp
End With
Next sh
End Sub
この状態では、マクロの記録で出来たコードと大差ありません。 例えば、 先にフィルタがかかっていたらうまく動かない可能性がありますし 大切なデータが、すべて消えてしまう可能性もあります。 記録をとったのと違う状態のものが出てきたら エラーが発生したり、意図した動きと違う動きをする可能性もあります。
実用を考えると、状況によってはもう少し 確認作業を入れるのが良いのかもしれません。
例えば データはあるか？ 事前にオートフィルタの設定はされていないか？ 等の事前確認。
(HANA)
＞等に注意しながら不要部分を削除すると をもう少し詳しく書いてみます。
今後マクロを作成する際も参考にできると思いますので どういったことなのか、理解していただければと思います。
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
この Selection は、その上で Rows("2:2") を Select しているので
Rows("2:2") のことです。
ですから、
◆Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
と一行にすることができます。
Range("A3").Select
Selection.AutoFilter
この Selection も、その上で Range("A3") を Select しているので
▲Range("A3").AutoFilter
になります。
Rows("4:4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection が二回出てきますが、二回とも Rows("4:4") の事ですね。
●Range(Rows("4:4"), Rows("4:4").End(xlDown)).Select
ActiveSheet.Range("$A$3:$B$10").AutoFilter Field:=2, Criteria1:="<>*1丁目*", _
この部分を
◆Range("A3").AutoFilter Field:=2, Criteria1:="<>*1丁目*", _
に変更できる事は、以前のバージョンでの記録結果を知っていないと
難しいかもしれません。。。
ここで A3 セルと指定することで、上の▲の行が不要になります。
Selection.Delete Shift:=xlUp
ここで、「選択範囲の行を削除」するわけですが
この段階で選択されている範囲は
●Range(Rows("4:4"), Rows("4:4").End(xlDown)).Select
です。
4行目を選択して、Ctrl + Shift + ↓ で範囲を選択したあとは 絞り込み作業を行っただけで、どこかの範囲をSelectする行動は 行っていませんので。
つまり、この Selection というのが
●Range(Rows("4:4"), Rows("4:4").End(xlDown))
ですので、先の該当部分は この位置まで移動させることができます。
（記録を取る段階では、前後させることができないのですが。。。。）
●の行が不要になって
◆Range(Rows("4:4"), Rows("4:4").End(xlDown)).Delete Shift:=xlUp
あとの二行
◆Range("A3").AutoFilter
◆Rows("2:2").Delete Shift:=xlUp
は、これまでの省略方法とほとんど同じです。
状況によっては 記録手順に気をつけて記録を取って こんな感じで Select・・・Selection をなくすだけでも かなりのコードは出来る様になると思います。
ループ処理はどうしても記録ではできませんし エラー処理も記録では入れられませんので そのあたりは別途追加することになりますが。
また「End(xlDown)」では、間に空行があった場合 そこまでしか移動しませんので、気を付けてください。
それから、何度も書きますが あくまでも 記録を基にしたコードであることを 忘れずに使用してください。 記録をとったのと違う状況のデータや環境では 期待する結果を得られない場合がありますし 大切な（必要な）データが消えてしまう可能性もあります。
(HANA)
ご丁寧にありがとうございます。 一度記録してからいろいろできるのですね・・・。
頭が固いのでなかなかすんなり理解できるかわかりませんし、おっしゃる通り会社でうまくいくかはわかりませんが、HANA様の教えてくださったことをプリントして頑張ってみます。 何回もご迷惑おかけして、すいません。 お付き合いくださり、ほんとにありがとうございました！！
(やったみたい）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.