Results of 1 - 1 of about 48828 for A�����������������������... (0.009 sec.)
- [[20080511215003]]
- #score: 1420
- @digest: 060a536e029092346d415497d9d81cb9
- @id: 37781
- @mdate: 2008-05-19T16:32:54Z
- @size: 33084
- @type: text/plain
- #keywords: 件") (127854), 有限 (40013), 限会 (38837), 株式 (26339), ponta (24836), 式会 (22078), 数a (21363), autofilter (20483), 社[ (18749), mytimer (16973), autofiltermode (16076), sheets (12663), operator (10818), field (9856), xland (9751), mr (9118), 会社 (8089), criteria1 (7218), sheet1 (7206), 変数 (6587), テッ (5631), 条件 (5452), トフ (4439), 弥太 (4181), オー (3792), ルタ (3734), xlup (3451), rows (3274), ステ (3217), フィ (3128), delete (3123), ィル (2976)
- 『変数の代入』（PONTA）
またまたお世話になります。 今日は変数（文字列）の代入についてお聞きしたいのですが やりたいことはシート「Sheet1」のA列にシート「条件」 のA列の値を含んでいた場合、その行を削除したいと思っています。 しかし変数の記述が間違っていると思うのですがうまく動きません。 全てのデータが消えてしまいます。 テストデータでの条件は2つですが実際は複数あり その部分も効率的に記述できないでしょうか?宜しくお願いします。 Shert1 [A] [B] [1] 顧客 数値 [2] AAA 324 [3] 株式会社あいう 435 [4] 有限会社たたた 256 [5] おおお株式会社 325 [6] QQQ 263 [7] BBB 3452 [8] CCC 572 [9] www 5156 [10] PPP 453 [11] PPP株式会社 445 条件 [A] [1] 株式会社 [2] 有限会社 [3] ** [4] ** [5] ** Sub test1() Dim i As Long, a As String, b As String i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row a = Sheets("条件").Range("A1").Value b = Sheets("条件").Range("A2").Value With Sheets("Sheet1") .[A1:A2].AutoFilter Field:=1, Criteria1:="=* & a & *", Operator:=xlAnd .Rows("2:" & i).Delete Shift:=xlUp .[A1:A2].AutoFilter Field:=1 .[A1:A2].AutoFilter Field:=1, Criteria1:="=* & b & *", Operator:=xlAnd .Rows("2:" & i).Delete Shift:=xlUp .[A1:A2].AutoFilter Field:=1 End With End Sub ---- 最初に質問ですが、このコードを実行する前は 必ずオートフィルタがかかって【いない】状態ですか? ＞変数の記述が間違っていると思うのですがうまく動きません。 に関しては ＞"=* & a & *" この部分は「* & a & *」を捜していますよ。 aがその様な文字ではない（変数）なら、 aの前に "=*" aの後ろに "*" を「&」でつなげてください。 ツール(T)→マクロ(M)→マクロ(M)から、ステップイン(S)を選択するか 製作中のコードの中にカーソルを置いた状態で[ F8 ]を押して、 コードを実行しながら確認してみると良いと思います。 ＞.[A1:A2].AutoFilter Field:=1, Criteria1:="=* & a & *", Operator:=xlAnd のコードの次へ移ったときに、エクセルに戻って A列のオートフィルタのオプションを開いてみると 「 & a & 」を「含む」となっているのが確認できます。 ＞テストデータでの条件は2つですが実際は複数あり 全てA列に対する物でしょうか? 例えば、↓変数aに 条件シートのA列のデータを次々に入れていきます。 Sub 代入() Dim i As Long, mr As Long, a As String With Sheets("条件") mr = .Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To mr a = .Range("A" & i).Value MsgBox "セルA" & i & "の値は " & a & " です。" Next End With End Sub (HANA) ---- HANA様 回答ありがとうございます。 しかし私の理解が足らず、下記CODEで実行してみましたが 結果は以下の通りとなってしまいました。どこがおかしいのでしょうか? Sub test1() Worksheets("Sheet1").AutoFilterMode = False Sheets("Sheet1").[A1:B1].AutoFilter Dim i As Long, a As String, b As String i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row a = Sheets("条件").Range("A1").Value b = Sheets("条件").Range("A2").Value With Sheets("条件") mr = .Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To mr a = .Range("A" & i).Value Next End With With Sheets("Sheet1") .[A1:A2].AutoFilter Field:=1, Criteria1:="=* & =*a* & *", Operator:=xlAnd .Rows("2:" & i).Delete Shift:=xlUp .[A1:A2].AutoFilter Field:=1 End With End Sub 結果 [A] [B] [1] 顧客 終了 [2] 有限会社たたた 256 [3] おおお株式会社 325 [4] QQQ 263 [5] BBB 3452 [6] CCC 572 [7] www 5156 [8] PPP 453 [9] PPP株式会社 445 ---- 先ず初めに変数を宣言セクションに（test1の下の行）移します。 変数aをStringからVariantに変更します。 最下行取得用の変数mrをLongで宣言追加します。 変数bは不要になりますので削除しときます。 Sheet1の作業をしている2行をWithで括ったところへ移動しませう。 .以降の記述で済む事はご存じでっしゃろ? それから条件のシートに作業を移します。 その際配列変数の領域を確保します。 ReDim a(1 To mr)といった塩梅に。 これから推察すればお分かりになりまっしゃろけど、当然mrの値を取得した後に記述 せにゃぁなりまへん。 その配列変数aにシート条件のA列に書かれたデータを格納していきます。 a(i)=..... という具合に。 それが終わると次はSheet1の作業に移ります。 今度は変数mrにSheet1の最下行を格納します。 先ほど移動した2行の上でも下でも宜しいと思われます。 配列変数aの1番目から最後までForで廻します。 For i=1 to Ubound(a) とすればaに格納された最後尾まで汗かきながらやってくれます。 Criteria1:= に指名する順序は "*" & a(i) & "*" こうなりますわなぁ。 A2からBの最下行をCells.SpecialCells(xlCellTypeVisible).Deleteというメソッドを 利用して行削除します。行全体の削除ですからShift:=xlUpはなくとも勝手にやってく れます。 シゴトが一段落したらフィルターモードをFalseにして終了ですワ。 行を削除するたんびに確認のコメントが表示されて煩わしいと思ったら Application.DisplayAlerts をヘルプで調べてみませう。 また削除で画面がちらつきますから、それを回避するには Application.ScreenUpdating もヘルプで調べてみれば宜しいかと思います。 元に戻すコードを忘れぬよう注意しませう。 書き込むのは上の方でFalseにし、End Subの手前で元に戻しませう。 （弥太郎） ---- （弥太郎）様 回答ありがとうございます。 文章の意味を理解しながら、トライしてみます。(PONTA) ---- 「ステップインで実行」やってみましたか? これは、作ったコードがどの様に動いているか 確認できる一つの方法です。 この方法を身につけると、「何故動かないか」が 分かりやすくなると思います。 思い通りに動かない点1 先に私が載せたコードは、サンプルコードです。 「この様にすれば、変数aに次々にセルの値を入れることが出来ますよ」 と言うだけの物です。 変数aにセルの値を入れて、入っている値でオートフィルタを実行したいなら 入れた直後に使用する必要があります。 Next End With で、最後まで終わった後ですと 最後に入れた値しか入っていませんよ。 ステップインで実行中、変数aに何が入っているか?は 「a」と言う文字にマウスを持っていくことで確認出来るでしょう。 しばらく待っていると、黄色い四角が出てきますので。 思い通りに動かない点2 弥太郎さんも書いて居られますが ＞ aの前に "=*" aの後ろに "*" を「&」でつなげてください。 というのは "=*" & a & "*" と言う事です。 最初の「"=* & a & *"」との違いをご確認下さい。 一つずつ変数に入れるなら、入れなくても同じなので 「"=*" & Sheets("条件").Range("A" & i).Value & "*"」 せっかくなので、弥太郎さんが書いて居られるようにするのが 良いと思います。 ↓ステップインの方法です。こちらもご参考に。 http://www.asahi-net.or.jp/‾ef2o-inue/vba_k/sub04_200.html PONTAさんなら、ご自身で動くコードが 書けると思いますので、頑張って下さいね。 完成した暁には（行き詰まった際も）コードを載せて下さいね。 お待ちしていますから。 (HANA) ---- HANAさま 丁寧な回答ありがとうございます。 >テストデータでの条件は2つですが実際は複数あり >全てA列に対する物でしょうか? 全てA列だけに対応します。 行き詰まりました。あれからいろいろやってみたのですが test4 では 以下の結果となり、ステップインで実行しながら、いろいろと試しましたが うまく動きません。test4が行き詰った状態のOCDEです 弥太郎さま 解読しようとヘルプを見ながらトライしましたが、今の私の知識ではとても無理でした。 もう少しVBAを理解できた時また挑戦したいと思います。 (PONTA) ＜test4の結果＞・・・有限会社を含む行が残る。 [A] [B] [1] 顧客 終了 [2] AAA 324 [3] 有限会社たたた 256 [4] QQQ 263 [5] BBB 3452 [6] CCC 572 [7] www 5156 [8] PPP 453 Sub test4() Worksheets("Sheet1").AutoFilterMode = False Dim i As Long, a, mr i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row a = Sheets("条件").Range("A1").Value mr = Sheets("条件").Cells(Rows.Count, 1).End(xlUp).Row Sheets("Sheet1").[A1:B1].AutoFilter With Sheets("Sheet1") .[A1:A2].AutoFilter Field:=1, Criteria1:="=*" & a & "*", Operator:=xlAnd .Rows("2:" & i).Delete Shift:=xlUp .[A1:A2].AutoFilter Field:=1 End With With Sheets("条件") For i = 1 To mr a = .Range("A" & i).Value Next End With End Sub ---- う〜ん、難しいですねえ。 変数aに関する所の流れを追ってみましょうか。 Shere1 [A] [B] 条件 [A] [1] 顧客 数値 [1] 株式会社 [2] AAA 324 [2] 有限会社 [3] 株式会社あいう 435 [3] ** [4] 有限会社たたた 256 [4] ** [5] おおお株式会社 325 [5] ** [6] QQQ 263 [7] BBB 3452 [8] CCC 572 [9] www 5156 [10] PPP 453 [11] PPP株式会社 445 最初は ＞ a = Sheets("条件").Range("A1").Value ですから、a は 「株式会社」ですよね。 ＞.[A1:A2].AutoFilter Field:=1, Criteria1:="=*" & a & "*", Operator:=xlAnd で、「株式会社」が含まれる行（3,5,11）が抽出され それに続くコードでその行が削除されますよね。 ●1● Sheet1は↓の状態 Sheet1 [A] [B] 条件 [A] [1] 顧客 数値 [1] 株式会社 [2] AAA 324 [2] 有限会社 [3] 有限会社たたた 256 [3] ** [4] QQQ 263 [4] ** [5] BBB 3452 [5] ** [6] CCC 572 [7] www 5156 [8] PPP 453 [9] [10] [11] 次に実行されるのは ＞ With Sheets("条件") ＞ For i = 1 To mr ＞ a = .Range("A" & i).Value ＞ Next ＞ End With の部分です。 まず、a に A1セルの値「株式会社」を入れ ←★1 次に、a に A2セルの値「有限会社」を入れ ←★2 i が mrまで行ったら終了しますので コードの次の行・・・・ ＞ End Sub ・・・終わってしまいましたね・・・。 処理の流れを考えてみましょう。 まずA1セルの「株式会社」が含まれる行を抽出し削除 次にA2セルの「有限会社」が含まれる行を抽出し削除 以下、A3,A4とデータが有る場合は続く・・・・。 のですよね。 『が含まれる行を抽出し削除』は ●1● の部分です。 現在のコードは 変数aの値として最初に「株式会社」を入れて●1●を実行した後に 確かに変数aの値を変更するコードは有りますが、 変更した後に●1●を実行するように成っていませんよね? ★1で 変数aにセルA1の値を代入したら ●1● を実行 ★2で 変数aの値をA2の値に変更したら ●1● を実行 ・・・・と言うコードにする必要があります。 Sheets("条件")は一回しか出てこないので省略せずに ＞ For i = 1 To mr ＞ a = Sheets("条件").Range("A" & i).Value ＞ Next の様に書く事にして・・・・ 良いですか? この部分で、A1の値をaに入れたら ●1● を実行 処理が終わったら Next で次のiに移ります。 A2の値がaに入るので ●1● を実行 処理が終わったら Next で次のiに移って行き、mr回処理を続けます。 ・・・出来そうですかね? これが出来たら、もう一つステップアップですので 安心せずにしばらく見に来てくださいね。 (HANA) ---- HANAさま 理解不足で、なぜかtest5もうまく動きません。 test5では .Rows("2:" & i).Delete Shift:=xlUp の箇所で変数 i が1となりうまく行を削除できません。 test6で変数 i を 11 にすると正常に動きます。しかしそれなら変数を使う意味が・・・ どこがおかしいのでしょうか? (PONTA) 結果 [A] [b] [1] AAA 324 [2] 有限会社たたた 256 [3] おおお株式会社 325 [4] QQQ 263 [5] BBB 3452 [6] CCC 572 [7] www 5156 [8] PPP 453 [9] PPP株式会社 445 Sub test5() Worksheets("Sheet1").AutoFilterMode = False Dim i As Long, a, mr i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row a = Sheets("条件").Range("A1").Value mr = Sheets("条件").Cells(Rows.Count, 1).End(xlUp).Row Sheets("Sheet1").[A1:B1].AutoFilter For i = 1 To mr a = Sheets("条件").Range("A" & i).Value With Sheets("Sheet1") .[A1:A2].AutoFilter Field:=1, Criteria1:="=*" & a & "*", Operator:=xlAnd .Rows("2:" & i).Delete Shift:=xlUp'・・・＞ここで i が1になる!!! .[A1:A2].AutoFilter Field:=1 End With Next End Sub Sub test6() Worksheets("Sheet1").AutoFilterMode = False Dim i As Long, a, mr i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row a = Sheets("条件").Range("A1").Value mr = Sheets("条件").Cells(Rows.Count, 1).End(xlUp).Row Sheets("Sheet1").[A1:B1].AutoFilter For i = 1 To mr a = Sheets("条件").Range("A" & i).Value With Sheets("Sheet1") .[A1:A2].AutoFilter Field:=1, Criteria1:="=*" & a & "*", Operator:=xlAnd .Rows("2:11").Delete Shift:=xlUp'・・・＞ i を11にすると正常に動く .[A1:A2].AutoFilter Field:=1 End With Next End Sub ---- PONTAさん、同じ変数を使っちゃ駄目ですよ。（涙） ・・・と言うか、気付きませんでした ごめんなさい。 えっと ＞i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row これが、データの最終行を求めた i ですよね。 最終行を求めて、せっかく i に入れたのに その後に ＞For i = 1 To mr とすると、i は「1」に成ってしまいます。 iが「1」に成った後に ＞.Rows("2:" & i).Delete Shift:=xlUp をしたら、当然 i は「1」ですよね。 (HANA) ---- HANAさま >PONTAさん、同じ変数を使っちゃ駄目ですよ。（涙） たしかにその通りです。（T T）あまりの単純なミスに私も泣けてきました。 変数をkにしてトラブル解決です。 >これが出来たら、もう一つステップアップですので >安心せずにしばらく見に来てくださいね。 次のステップが気になります・・・(PONTA) ---- ここまでこれたのですから、次のステップも 簡単にクリア出来ることでしょう。 と、その前に・・・。 ステップインの実行方法は分かりましたよね。 VBEには「ローカルウィンドウ」と言うのを表示していますか? 表示していない場合は、表示(V)からローカルウィンドウ(S) を選んで表示して下さい。 ここで、各変数にその時何が入っているか確認出来ます。 （マウスを変数に持っていって しばらく待たなくても。） 今回は ＞.Rows("2:" & i).Delete Shift:=xlUp'・・・＞ここで i が1になる!!! のが問題でしたよね? でもどこで「1」に成るのか分からなかったですよね。 そんなときに、ステップインで[ F8 ]キーを押しながら 一行ずつ実行させて、ローカルウィンドウで iの値の移り変わりを観察します。 すると、最初は「11」が入っていたのに ＞For i = 1 To mr が済んだ時に「1」に変わってしまうので この行がおかしい事が分かるようになると思います。 さて、コードの話しに戻りますが、もう一度良く見て下さい。 ＞a = Sheets("条件").Range("A1").Value で、A1の値を入れますが 一回目の ＞a = Sheets("条件").Range("A" & i).Value で、A1の値を入れますよね。 まぁ、後ろのiを2から初めても良いのですが 前の部分を削除する方が考えがスムーズに行くように思います。 次のステップは、弥太郎さんが書いて居られる ＞その配列変数aにシート条件のA列に書かれたデータを格納していきます。 ＞a(i)=..... という具合に。 の部分に近い部分です。 一つずつ入れるのではなく、一気に入れてしまおうと思うので 少し違いますけどね。 すぐ上に書いてある事 （前の部分を削除する方が・・・の話しです） とは、又違った話ですので 頭をリセットして読んで下さい。 「格納」する利点としては、 1.処理をするのにあちこちのシートを見なくて良い 2.セルにデータを見に行く時間を短縮出来る 3.処理行数と、処理回数を合わせる事が出来る 等があげられると思います。 丁度変数aはVariantに成っていますから このまま使用します。 変数mrや、新しい変数kは、数値が入るので、iと同様に As Long を付けておいて下さいね。 変数aに 条件シートのA1:A2の範囲のデータを格納するには 条件 [A] [1] 株式会社 [2] 有限会社 a = Sheets("条件").Range("A1:A2") の様に書きます。 この時aは a <1> [1] 株式会社 [2] 有限会社 の様に成っています。 1行目の1列目に 株式会社 2行目の1列目に 有限会社 と入っています。 A1セルの値が何か?は RANGE("A1").VALUE と書きましたよね? 変数aの1行目の1列目の値は何か? は a(1 , 1) の様に書きます。 2行目の1列目の値は何か? は a(2 , 1) です。 注意する点は、やはり 行番号を先に書く と言う事だと思います。 逆にすると、変な所を見たり そんな場所はない!! と怒られたりするので 気を付けて下さいね。 今回は、データ範囲の最終行が未定です。 でも、何行有るかは ＞mr = Sheets("条件").Cells(Rows.Count, 1).End(xlUp).Row で求められますよね。 ですから、その範囲を変数aに格納して下さい。 a = Sheets("条件").Range("A1:A" & mr) の様な感じですよ。 勿論この a=・・・ を書く場所は ＞これから推察すればお分かりになりまっしゃろけど、 ＞当然mrの値を取得した後に記述せにゃぁなりまへん。 ですよ。 取得する前に書いた場合、mrは「0」ですから a = Sheets("条件").Range("A1:A0") なんて事に成ってしまいます。 それから、データは既にaに入っているのですから ＞a = Sheets("条件").Range("A" & i).Value は不要に成りますよね。 変わりに 変数aの何番目を見るか指定して下さい。 a(1 , 1) で 「株式会社」 a(2 , 1) で 「有限会社」 a(i , 1) が i番目に入っているデータです。 ですから ＞Criteria1:= に指名する順序は "*" & a(i) & "*" こうなりますわなぁ。 こうなるんです。 入れ方が少し違うので、"*" & a(i , 1) & "*" 「一列目の」が必要になりますが。‾‾‾ 取り敢えず、ここまででやってみて下さい。 上記説明は、動作確認をしながら書いている物ではないので そのまま書いただけでは上手く動かないかもしれませんが その点もふまえてやってみて下さい。 現在のコードでご希望の事は出来るのでしょうから こちらは焦らず ゆっくり行きましょう。 (HANA) ---- HANAさま ありがとうございます。 教えていただいたヒントで一旦CODE(test8)は完成しましたが >a = Sheets("条件").Range("A1:A" & mr) や >"*" & a(i , 1) & "*" を使い弥太郎さんの助言を含め test8とは違う考え方にトライしてみます。 (PONTA) Sub test8() Worksheets("Sheet1").AutoFilterMode = False Dim i As Long, a, mr As Long, k As Long, p As Long i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row a = Sheets("条件").Range("A1").Value mr = Sheets("条件").Cells(Rows.Count, 1).End(xlUp).Row Sheets("Sheet1").[A1:B1].AutoFilter For k = 1 To mr a = Sheets("条件").Range("A" & k).Value Sheets("Sheet1").[A1:B1].AutoFilter Field:=1, Criteria1:="=*" & a & "*", Operator:=xlAnd p = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row If p = 1 Then Worksheets("Sheet1").AutoFilterMode = False Sheets("Sheet1").[A1:B1].AutoFilter Exit Sub End If Sheets("Sheet1").Rows("2:" & i).Delete Shift:=xlUp Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 Next End Sub ---- はっっ、そちらが「k」でしたか・・・・。 取りあえず、test8に関してですが・・・・ 上にも書きましたが ＞a = Sheets("条件").Range("A" & k).Value が有るので ＞a = Sheets("条件").Range("A1").Value は不要ですよね? また、新たに追加された ＞If p = 1 Then の処理ですが、これって 「抽出されなかったら」 の分岐ですよね? ＞Exit Sub すると、例えば「株式会社」は無いけど「有限会社」は有る なんて時に、 1.「株式会社」で抽出 2.p = 1 なので Exit Sub（マクロ終了） となって、「有限会社」は残りませんか? なので、 p<>1 の時に消す作業をする事にするのが 良いように思いますが・・・。 (HANA) ---- HANAさま ご指摘通り ＞a = Sheets("条件").Range("A" & k).Value は必要ありませんでした(^^;) p = 1 は条件に何も該当しなかった場合、見出し行のみが残る(1行目)と思い p = 1としましたが、シート「条件」のA列に何もデータが無い場合にSheet1の データが消えてしま事が判明しました。通常の環境では1行目に削除の条件が くることが無いので気づきませんでした。もう少し良く考えて見ます。 ご指摘ありがとうございます。(PONTA) ---- ＞通常の環境では1行目に削除の条件がくることが無いので 「削除しない条件」でしょうか?? いくつかのパターンを想定してコードを考えると ＞ a = Sheets("条件").Range("A" & k).Value で、aにセルの値を入れますよね。 この後、IFを使って 「aが""で無いとき」現在の処理を行う事にします。 aが""だったら、処理をせずに次のiに行きます。 処理が進んで ＞ p = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row で、フィルタ後の最終行を求めますよね。 この後、IFを使って 「pが0 で無いとき」削除の処理を行います。 pが0 だったら、処理をせずに次のiに・・・・。 と言う流れにすると良いかもしれません。 1.マクロコードに名前を付ける 2.変数の宣言と型の指定 3.Sheet1のオートフィルタを解除 4.Sheet1の最終行（i）を求める 5.条件シートの最終行（mr）を求める 6.Sheet1にオートフィルタをかける 7.ループ処理を開始する 8. aに検索する文字を入れる 9. aが空欄でないか判定し 空欄でない場合は 10. 抽出 11. オートフィルタ後の最終行（p）を求める 12. pが0でないか判定し 0でない場合は 13. 2行目からi行目を削除 14. pの条件処理を終了 15. aの条件処理を終了 16.次のiに関して同様の処理を行う 17.1列目でフィルタがかかっているので全て表示させる 18.マクロコードの終了 (HANA) ---- HANAさま 丁寧な助言ありがとうございます。 Test10でシート「条件」の文字列が重複した場合や、条件に空白がランダムに入っていた場合や シート「Sheet1」のA列に無い文字列がシート「Sheet1」のA列に含まれていた場合の エラーも処理する事が出来ました。 大変参考になりました。ありがとうございます。 Sub test10() Worksheets("Sheet1").AutoFilterMode = False Dim i As Long, a, mr As Long, k As Long, p As Long i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row mr = Sheets("条件").Cells(Rows.Count, 1).End(xlUp).Row Sheets("Sheet1").[A1:B1].AutoFilter For k = 1 To mr a = Sheets("条件").Range("A" & k).Value If a <> "" Then Sheets("Sheet1").[A1:B1].AutoFilter Field:=1, Criteria1:="=*" & a & "*", Operator:=xlAnd p = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row If p <> 1 Then Sheets("Sheet1").Rows("2:" & i).Delete Shift:=xlUp Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 Else Worksheets("Sheet1").AutoFilterMode = False Sheets("Sheet1").[A1:B1].AutoFilter End If End If Next End Sub ---- 安心して動かせるコードが出来ましたね。 でも、上で私が書いたステップでも出来ますので 試してみてください。 ちなみに、今回の場合 ＞Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 と ＞Worksheets("Sheet1").AutoFilterMode = False ＞Sheets("Sheet1").[A1:B1].AutoFilter の結果の違いは何だと思って使い分けて居られるのですか? そうそう、変数aは 今回は「Sheets("条件").Range("A" & k).Value」 が入っていくので、変数の型は String にしておくのが良いのかもしれませんね。 (HANA) ---- HANAさま コメントありがとうございます。 ＞Worksheets("Sheet1").AutoFilterMode = False ＞Sheets("Sheet1").[A1:B1].AutoFilter の違いですが ＞Worksheets("Sheet1").AutoFilterMode = False はオートフィルタがかかっているかいないか分からない場合に使用 ＞Sheets("Sheet1").[A1:B1].AutoFilter は確実にオートフィルタがかかっている場合に使用すると思っていました。 が、まれに＞Sheets("Sheet1").[A1:B1].AutoFilterではオートフィルタの 解除が出来ない事があったので、自動記録で問題なくフィルタを解除できるときは ＞Sheets("Sheet1").[A1:B1].AutoFilter をそのまま使用し 直接シートモジュールに書き込むときは ＞Worksheets("Sheet1").AutoFilterMode = False を使用しています。そのため＞Worksheets("Sheet1").AutoFilterMode = False とする必要の無い箇所でも＞Worksheets("Sheet1").AutoFilterMode = False を使う事があります。 Test9でHANAさまから教えていた手順通りCODEを書いたのですが どこか私が勘違いをしているようで、1回目のマクロ実行はうまくいくのですが 2回目の実行（Sheet1に削除するデータが無い状態）では2行目以降が全て消えてしまいました。 それで ＞12. pが0でないか判定し 0でない場合は の部分をpが1でないかを判定するよう変更しました。 Sub test9() Dim i As Long, a, mr As Long, k As Long, p As Long Worksheets("Sheet1").AutoFilterMode = False i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row mr = Sheets("条件").Cells(Rows.Count, 1).End(xlUp).Row Sheets("Sheet1").[A1:B1].AutoFilter For k = 1 To mr a = Sheets("条件").Range("A" & k).Value If a <> "" Then Sheets("Sheet1").[A1:B1].AutoFilter Field:=1, Criteria1:="=*" & a & "*", Operator:=xlAnd p = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row If p <> 0 Then Sheets("Sheet1").Rows("2:" & i).Delete Shift:=xlUp Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 Else Worksheets("Sheet1").AutoFilterMode = False Sheets("Sheet1").[A1:B1].AutoFilter End If End If Next End Sub (PONTA) ---- 失礼しました・・・。（汗） ＞この後、IFを使って 「pが0 で無いとき」削除の処理を行います。 の部分は 確かに ＞pが1でないかを判定するよう変更 でした・・・orz （ちょっと別の事を考えてまして・・・・。←言い訳） ですので、その部分はしっかり変更してください。 ですが ＞が、まれに＞Sheets("Sheet1").[A1:B1].AutoFilterではオートフィルタの ＞解除が出来ない事があったので、 と言うのは・・・?? このコードはオートフィルタのON/OFFを切り替えるコードです。 設定されている状態で実行すれば解除され 解除されている状態で実行すれば設定されます。 「解除できない事があった」時のこのコードを実行する前は オートフィルタが設定された状態でしたか? 重複しますが、オートフィルタが設定されていない状態から ＞Sheets("Sheet1").[A1:B1].AutoFilter を実行すると、オートフィルタが設定されますので 「解除されていない」状態になります。 オートフィルタが設定されているか、されていないか分からない状態から 設定されていない状態にしたい場合は ＞Worksheets("Sheet1").AutoFilterMode = False を使うのが安全だと思います。 でも、例の部分ではその直ぐ後に ＞Sheets("Sheet1").[A1:B1].AutoFilter とオートフィルタを設定していますよね? ちなみに、私が書いたのは ＞Sheets("Sheet1").[A1:B1].AutoFilter ではなく ＞Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 なんですけどね。 (HANA) ---- HANAさま オートフィルタの件、私が勘違いをしていました。 ＞Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 はオートフィルタ自体は解除せず、今回の場合はA1の オートフィルタを全て選択した状態に戻すという意味だと思うのすが・・・!? あっそうですね、そうするとtest10の Else の後は >Worksheets("Sheet1").AutoFilterMode = False >Sheets("Sheet1").[A1:B1].AutoFilter ではなく >Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 で良いですね。これで更にCODEがすっきりしました。ありがとうございます。(PONTA) ---- ＞あっそうですね、 そうです!! でも、もう一回立ち止まってください。 今回のコードは、A列でのみ抽出します。 その際に、次の検索をする前に ＞オートフィルタを全て選択した状態に戻す 必要がありますか? 一番最後に、削除された状態で終わっては 何も表示されないので、全て選択 して 全ての行を表示させておく必要は有るでしょうけど・・・。 (HANA) ---- HANAさま コメントありがとうございます。 ＞オートフィルタを全て選択した状態に戻す ＞必要がありますか? もし戻さないとすると以下のようなCODEになると思うのですが If p <> 1 Then Sheets("Sheet1").Rows("2:" & i).Delete Shift:=xlUp Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 Else End If 確かに Else の後の＞Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 が無くても目的は果たしますが ＞一番最後に、削除された状態で終わっては ＞何も表示されないので、全て選択 して ＞全ての行を表示させておく必要は有るでしょうけど・・・。 結果を見るときに、オートフィルタを手動で全て選択する必要があるので CODEの中に入れてみました。 たくさんの助言や、ご指摘ありがとうございます。 (PONTA) ---- それがしも板汚しに顔出した手前、こんなやり方も有るっちゅう程度で呈示 しときます。 新しいブックを開いて下のコードをコピペします。 最初に「データ作成」を実行しますと10000行のデータがSheet1に並びます。 条件 というSheetを作成し、A列の1行目からA,B,C,D,E,F,G,H と、まぁ適当 にデータを並べてみてくらはい。 Sheet1に戻って、先ずtest10を実行します。 今一度「データ作成」を実行して、今度はNone_Filterを実行してみてくらはい。 （弥太郎） '----------------------- Sub None_Filter() Dim i As Long, mr As Long, j As Long, Rex As Object, a, data As String, x Set Rex = CreateObject("vbscript.regexp") mytimer = Timer With Sheets("条件") a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)) For i = 1 To UBound(a, 1) If Not IsEmpty(a(i, 1)) Then data = data & a(i, 1) & "|" End If Next data = Left(data, Len(data) - 1) End With With Sheets("sheet1") Rex.Pattern = "(" & data & ")" tbl = .Range("a1").Resize(.Range("a" & Rows.Count).End(xlUp).Row, 2) ReDim x(1 To UBound(tbl, 1), 1 To UBound(tbl, 2)) For i = 1 To UBound(tbl, 1) If Not Rex.test(tbl(i, 1)) Then j = j + 1 For n = 1 To UBound(tbl, 2) x(j, n) = tbl(i, n) Next n End If Next i .Cells.Clear .Cells(1, 1).Resize(j, UBound(tbl, 2)) = x End With Set Rex = Nothing mytimer = Timer - mytimer MsgBox Format(mytimer, "##0.00") & "秒" End Sub Sub データ作成() Dim i As Long, x ReDim x(1 To 10000, 1 To 2) For i = 1 To 10000 For n = 1 To 2 If n = 1 Then x(i, n) = Chr(65 + Int(Rnd * 26)) & Chr(65 + Int(Rnd * 26)) _ & Chr(65 + Int(Rnd * 26)) & Chr(65 + Int(Rnd * 26)) Else x(i, n) = Int(Rnd * 1000) End If Next n Next i Sheets("sheet1").Cells(1, 1).Resize(, 2) = Split("見だし1 見だし2") Sheets("sheet1").Cells(2, 1).Resize(10000, 2) = x End Sub Sub test10() Worksheets("Sheet1").AutoFilterMode = False Dim i As Long, a, mr As Long, k As Long, p As Long i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row mr = Sheets("条件").Cells(Rows.Count, 1).End(xlUp).Row mytimer = Timer Sheets("Sheet1").[A1:B1].AutoFilter For k = 1 To mr a = Sheets("条件").Range("A" & k).Value If a <> "" Then Sheets("Sheet1").[A1:B1].AutoFilter Field:=1, Criteria1:="=*" & a & "*", Operator:=xlAnd p = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row If p <> 1 Then Sheets("Sheet1").Rows("2:" & i).Delete Shift:=xlUp Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 Else Worksheets("Sheet1").AutoFilterMode = False Sheets("Sheet1").[A1:B1].AutoFilter End If End If Next mytimer = Timer - mytimer MsgBox Format(mytimer, "##0.00") & "秒" End Sub ---- 私が書いたのは ＞次の検索をする前に ＞＞オートフィルタを全て選択した状態に戻す ＞必要がありますか? ですので、 ＞もし戻さないとすると If p > 0 Then Sheets("Sheet1").Rows("2:" & i).Delete Shift:=xlUp End If の様になります。 ＞Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 は必要ない事になったのでしょ? ここが、12.13.14.がコードに成った部分です。 でも ＞一番最後に、全ての行を表示させておく必要は有るでしょうけど・・・。 と言うことで、18.の前に17の処理を入れます。 今回PONTAさんが作成して居られる考え方を元に 1〜18ステップを言葉にしましたが、コードにすると ↓ Sub test11() Dim i As Long, a As String, mr As Long, k As Long, p As Long Worksheets("Sheet1").AutoFilterMode = False i = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row mr = Sheets("条件").Cells(Rows.Count, 1).End(xlUp).Row Sheets("Sheet1").[A1:B1].AutoFilter For k = 1 To mr a = Sheets("条件").Range("A" & k).Value If a <> "" Then Sheets("Sheet1").[A1:B1].AutoFilter Field:=1, Criteria1:="=*" & a & "*", Operator:=xlAnd p = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row If p > 0 Then Sheets("Sheet1").Rows("2:" & i).Delete Shift:=xlUp End If End If Next Sheets("Sheet1").[A1:B1].AutoFilter Field:=1 End Sub 全て表示させるコードは、17行目に一度だけ使用します。 抽出とその処理が終わった直後に、毎回全て表示させる必要は無いのですから。 無駄な動きを少なくすると、処理速度が上がると思います。 (HANA) ---- HANAさま 弥太郎さま 弥太郎さまにご教示いただいたコードは私のレベルでは解読に時間がかかりそうです! しかし、「データ作成」で作成したデータで比べてみると確かに早いです。数倍は早い! 早く配列を使用したコードにも慣れていきたいと思います。 HANAさま >無駄な動きを少なくすると、処理速度が上がると思います。 まだまだ私は基本が出来ていないようです。 その通りだと思いますので、ただ動くCODEから早く正確に動くCODEを 書けるように基本を身につけます。お二方ともありがとうございます。 (PONTA)... - http://www.excel.studio-kazu.jp/wiki/kazuwiki/200805/20080511215003.txt - [detail] - similar
PREV
NEXT
Powered by Hyper Estraier 1.4.13, with 97012 documents and 608126 words.
訪問者：