Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
735 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【78620】特定の文字列の入っている行を削除したいです piropiro 16/12/1(木) 12:08 質問[未読]
【78621】Re:特定の文字列の入っている行を削除した... β 16/12/1(木) 13:02 発言[未読]
【78622】Re:特定の文字列の入っている行を削除した... β 16/12/1(木) 13:09 発言[未読]
【78623】Re:特定の文字列の入っている行を削除した... piropiro 16/12/1(木) 13:46 質問[未読]
【78624】Re:特定の文字列の入っている行を削除した... β 16/12/1(木) 15:01 発言[未読]
【78625】Re:特定の文字列の入っている行を削除した... piropiro 16/12/1(木) 15:15 質問[未読]
【78626】Re:特定の文字列の入っている行を削除した... β 16/12/1(木) 15:23 発言[未読]
【78627】Re:特定の文字列の入っている行を削除した... piropiro 16/12/1(木) 15:39 質問[未読]
【78628】Re:特定の文字列の入っている行を削除した... β 16/12/2(金) 11:06 発言[未読]
【78629】Re:特定の文字列の入っている行を削除した... piropiro 16/12/2(金) 11:45 質問[未読]
【78630】Re:特定の文字列の入っている行を削除した... β 16/12/2(金) 13:38 発言[未読]
【78631】Re:特定の文字列の入っている行を削除した... piropiro 16/12/2(金) 14:15 質問[未読]
【78632】Re:特定の文字列の入っている行を削除した... β 16/12/2(金) 17:52 発言[未読]
【78636】Re:特定の文字列の入っている行を削除した... 佳 16/12/2(金) 22:12 発言[未読]
【78645】Re:特定の文字列の入っている行を削除した... piropiro 16/12/3(土) 19:03 質問[未読]
【78650】Re:特定の文字列の入っている行を削除した... piropiro 16/12/5(月) 11:17 質問[未読]
【78651】Re:特定の文字列の入っている行を削除した... β 16/12/5(月) 11:32 発言[未読]
【78653】Re:特定の文字列の入っている行を削除した... piropiro 16/12/5(月) 11:39 質問[未読]
【78654】Re:特定の文字列の入っている行を削除した... β 16/12/5(月) 14:01 発言[未読]
【78655】Re:特定の文字列の入っている行を削除した... piropiro 16/12/5(月) 14:17 質問[未読]
【78656】Re:特定の文字列の入っている行を削除した... β 16/12/5(月) 17:45 発言[未読]
【78657】Re:特定の文字列の入っている行を削除した... piropiro 16/12/5(月) 19:01 お礼[未読]
【78635】Re:特定の文字列の入っている行を削除した... γ 16/12/2(金) 22:11 発言[未読]
【78652】Re:特定の文字列の入っている行を削除した... piropiro 16/12/5(月) 11:35 質問[未読]
【78620】特定の文字列の入っている行を削除したい...
質問  piropiro  - 16/12/1(木) 12:08 -
引用なし
パスワード
   VBA初心者です。
よろしくお願いします。

以下のように書いたのですが、結果がおかしいのです。

Dim lastrow As Long
Dim i As Long, j As Long

lastrow = Cells(Rows.Count, 9).End(xlUp).Row

For i = lastrow To 4 Step -1

For j = 11 To 7000

If Cells(i, j) = "NA" Then
Rows(i).Delete

End If

Next j
Next i

範囲内にNAと入力されたセルがあったら
その行を削除したいのですが
NAがない行も削除されているのです。

どなたかアドバイスをお願いいたします！
【78621】Re:特定の文字列の入っている行を削除し...
発言  β  - 16/12/1(木) 13:02 -
引用なし
パスワード
   こちらで、そのまま実行しましたが、NAのない行は削除されないと思いますけど？
ただし、このコードには問題があります。

行 i に対して、4列目から7000列目までループでチェックし（この 7000 が意味のある数字なのかどうかは？？ですけど）
どこかの列にNA があって行削除すれば、もう、その行の判定は不要ですよね。削除してしまってますから。
なのに、コードは延々と 削除の結果繰り上がった空白行に対して判定を繰り返しています。
結果は、NAではないのでカラブリで、結果オーライですけど無駄ですね。
削除したら Exit For で　当該のループを強制脱出させなければいけません。

ところで、列ごとに NAかどうかのチェックをしていますが、シート関数のMATCHをVBAで利用したり
VBFのFindメソッドで、当該行の当該列領域に NA があるかないかを１行で判定して、あれば、それを削除。
こういう方法がおすすめです。

それと、コードにはインデントを付けましょう。
【78622】Re:特定の文字列の入っている行を削除し...
発言  β  - 16/12/1(木) 13:09 -
引用なし
パスワード
   ▼piropiro さん：

シート関数MATCHを使った処理例です。
ご参考まで。

Sub Sample()
　　Dim lastrow As Long
　　Dim i As Long, j As Long
　　Dim x As Variant
　　
　　lastrow = Cells(Rows.Count, 9).End(xlUp).Row

　　For i = lastrow To 4 Step -1

　　　　x = Application.Match("NA", Range(Cells(i, 11), Cells(i, 7000)), 0)
　　　　If IsNumeric(x) Then Rows(i).Delete
　　　　　　
　　Next i
　　
End Sub
【78623】Re:特定の文字列の入っている行を削除し...
質問  piropiro  - 16/12/1(木) 13:46 -
引用なし
パスワード
   ▼β さん：

こんな早く教えて頂けるなんて本当にありがとうございます。

最初はFindメソッドを使用してみたのですがうまくいかず。。。
お恥ずかしいですが、今の私の知識では先程のコードが精いっぱいでして。。。

MATCHを使うとこんなに処理がスムーズなんてすばらしいです。
まだ理解できていませんが、解読してみます！

それからもうひとつ質問なのですが
一つ一つ手動で検索→削除すると1128行のデータが残るのですが
VBAで実行すると1118行で10行分のデータが削除されてしまっているのです。
（NAは含まれていないのです）

先程の私のダメダメなコードでもβさんのMATCHのコードでもです。
βさんは「NAのない行は削除されないと思います」っと書いてくださいましたが
私のPCでは削除されてしまうのです。
お心当たりがありましたら教えてください。
よろしくお願いします。
【78624】Re:特定の文字列の入っている行を削除し...
発言  β  - 16/12/1(木) 15:01 -
引用なし
パスワード
   ▼piropiro さん：

う・・・ん・・・
どこかに（ずっと、右のほうに）NAがあると思うんですがねぇ。

以下のコードを走らせて出てくる列が、一番右側にNAがあった列です。

Sub 確認()
　　Dim c As Range
　　Dim f As Range
　　Dim col As Long
　　
　　Set c = Columns("K:JIF").Find(What:="NA", LookAt:=xlWhole)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If
　　
　　Set f = c
　　
　　Do
　　　　If c.Column > col Then col = c.Column
　　　　Set c = Columns("K:JIF").FindNext(c)
　　Loop While c.Address <> f.Address
　　
　　MsgBox "一番右のNAは " & Split(Columns(col).Address(False, False), ":")(0) & " 列にあります"
　　
End Sub
【78625】Re:特定の文字列の入っている行を削除し...
質問  piropiro  - 16/12/1(木) 15:15 -
引用なし
パスワード
   ▼β さ〜ん：

わざわざ確認コードまで。。。
ありがとうございます！
でも、NAなかったです。。。。
う〜ん、煮詰まってきました。

NAがないのに削除された行のデータをみてみましたが
他の行と特に変わったところはなく。。。
今現在はINC列までデータが入っているのですが
日によって増えたりするので7000列に指定しているのですが
これは関係ないですよね？
【78626】Re:特定の文字列の入っている行を削除し...
発言  β  - 16/12/1(木) 15:23 -
引用なし
パスワード
   ▼piropiro さん：

奇々怪々ですねぇ・・・

しばらくしたら外出しますので、明日以降になると思いますが
何か原因がないのか、さぐってみます。

最大列については、7000 と指定せず自動取得ができるのですが
それは、原因がわかってからにしましょう。
【78627】Re:特定の文字列の入っている行を削除し...
質問  piropiro  - 16/12/1(木) 15:39 -
引用なし
パスワード
   ▼β さま〜〜〜

ありがとうございます。
なんと！お優しい！！！！
全然明日でも大丈夫です。
βさまのご都合の良いときで。

よろしくお願いしますm(__)m
【78628】Re:特定の文字列の入っている行を削除し...
発言  β  - 16/12/2(金) 11:06 -
引用なし
パスワード
   ▼piropiro さん：

まだ原因はわかりません。
もう１つ確認してください。

>一つ一つ手動で検索→削除すると1128行のデータが残るのですが
>VBAで実行すると1118行で10行分のデータが削除されてしまっているのです。

このシートで以下のコードを走らせてください。
で、でてくるメッセージが正しい件数かどうかを確認してみてください。
（全体の行数と削除行数を表示しています。ですから引き算すれば残る行数になります）

Sub 確認2()
　　Dim a As Range
　　Dim r As Range
　　Dim c As Range
　　Dim f As Range
　　Dim x As Long
　　
　　'使用領域の K列から最終列までの列数
　　x = Range("A1", ActiveSheet.UsedRange).Columns.Count - 10
　　'判定対象領域
　　Set a = Range("I4", Range("I" & Rows.Count).End(xlUp)).Offset(, 2).Resize(, x)
　　
　　Set c = a.Find(What:="NA", LookAt:=xlWhole)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If
　
　　Set f = c
　
　　Do
　　　　If r Is Nothing Then
　　　　　　Set r = c
　　　　Else
　　　　　　Set r = Union(r, c)
　　　　End If
　　　　Set c = a.FindNext(c)
　　Loop While c.Address <> f.Address
　
　　MsgBox "対象領域の行数は " & a.Rows.Count & "行で、そのなかの削除対象は" & Intersect(r.EntireRow, Columns("K")).Count & "行です"
　
End Sub
【78629】Re:特定の文字列の入っている行を削除し...
質問  piropiro  - 16/12/2(金) 11:45 -
引用なし
パスワード
   ▼β さん：

早速ありがとうございます。

試しました！
実際の削除行は39行で
βさんのコードの結果では49行削除と表示されました。。。
やはり、10行分差異があります。。。
【78630】Re:特定の文字列の入っている行を削除し...
発言  β  - 16/12/2(金) 13:38 -
引用なし
パスワード
   ▼piropiro さん：

【魔の10行】ですかぁ。
不思議ですねぇ。

消されるべきなのに消されないということなら、いろいろ考えられますが
消されるべきではないのに消される・・・

可能性は極めて低いのですが、以下のケースなら、そうなります。

セルに NA という値があったとします。
で、表示書式設定で、文字列の場合に　別文字列　に設定すれば、実際のセルの値とは異なる値が
セルに表示されます。
さらに、ユーザー定義で　;;;　と設定すると、セルは空白に見えてしまいます。
なので、【ここにはNAはない！】
ところが　実態は NA ですから、セル比較（そちらのコード）やMATCH で、NA と認識します。
またFindメソッドでも LookIn を xlFormula にするとNAだと認識されます。

で、削除。見た目は NA ではないセルの行が削除されるわけです。
（コード上で LookIn を省略すると、たまたま、そのPCで、その前に行われた Findメソッド、Replaceメソッド
　ないしは　検索や置き換えの操作時のオプションが適用されます）

可能性は低いのですが、以下の確認3 と確認4 の結果は同じになりますか？
それとも10行の差異がありますか？

Sub 確認3()
　　Dim a As Range
　　Dim r As Range
　　Dim c As Range
　　Dim f As Range
　　Dim x As Long
　
　　 '使用領域の K列から最終列までの列数
　　 x = Range("A1", ActiveSheet.UsedRange).Columns.Count - 10
　　'判定対象領域
　　 Set a = Range("I4", Range("I" & Rows.Count).End(xlUp)).Offset(, 2).Resize(, x)
　
　　 Set c = a.Find(What:="NA", LookAt:=xlWhole, LookIn:=xlValues)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If

　　 Set f = c

　　 Do
　　　　If r Is Nothing Then
　　　　　　Set r = c
　　　　Else
　　　　　　Set r = Union(r, c)
　　　　End If
　　　　Set c = a.FindNext(c)
　　Loop While c.Address <> f.Address

　　 MsgBox "対象領域の行数は " & a.Rows.Count & "行で、そのなかの削除対象は" & Intersect(r.EntireRow, Columns("K")).Count & "行です"

End Sub

Sub 確認4()
　　Dim a As Range
　　Dim r As Range
　　Dim c As Range
　　Dim f As Range
　　Dim x As Long
　
　　 '使用領域の K列から最終列までの列数
　　 x = Range("A1", ActiveSheet.UsedRange).Columns.Count - 10
　　'判定対象領域
　　 Set a = Range("I4", Range("I" & Rows.Count).End(xlUp)).Offset(, 2).Resize(, x)
　
　　 Set c = a.Find(What:="NA", LookAt:=xlWhole, LookIn:=xlFormulas)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If

　　 Set f = c

　　 Do
　　　　If r Is Nothing Then
　　　　　　Set r = c
　　　　Else
　　　　　　Set r = Union(r, c)
　　　　End If
　　　　Set c = a.FindNext(c)
　　Loop While c.Address <> f.Address

　　 MsgBox "対象領域の行数は " & a.Rows.Count & "行で、そのなかの削除対象は" & Intersect(r.EntireRow, Columns("K")).Count & "行です"

End Sub
【78631】Re:特定の文字列の入っている行を削除し...
質問  piropiro  - 16/12/2(金) 14:15 -
引用なし
パスワード
   ▼β さん！！！！！！！！！

またまた早急のお返事、本当にありがとうございます。

コードは理解できてないですが（スミマセン＞＜）
確認3 と確認4 の両方とも実行しました。

結果、「そのなかの削除対象は39行です」と表示されました。
実際の削除行の39行です。

っと！っということは魔の10行解決？？？
βさんのご指摘通り、表示形式設定のせい？
ん？逆？解決してない？

スミマセン、混乱してきました。
【78632】Re:特定の文字列の入っている行を削除し...
発言  β  - 16/12/2(金) 17:52 -
引用なし
パスワード
   ▼piropiro さん：

確認3 も 確認4 も、どちらも 39 だったということですか？
そうであれば、なぞは謎のまま・・・

私の目論見は、確認3 は 39、でも　確認4 は 49 と出てほしかったんです。

piropiroさんの目視確認では、39行削除のはず。
でも、piropiroさんのコードや私のMATCHを使ったコードでは49行削除されてしまう！

こういうことですね。

で、私の推論は、削除行は 49 が正しい！　というものです。
つまり、NA とあきらかに記入されている行の他に、目には見えないけど、実は NA という
値を持つセルが 10行あって、それらも『正しく』削除して、49行削除。

じゃぁ、その目に見えない NA って何かということですけど、たとえば
セルの表示書式が ;;; だった場合、NA と入っているのに空白表示。
なので、人間は、見た目では気が付かない。

それを確認するために 確認3 と 確認4 を試してもらったんですが・・・
そうですかぁ・・どちらも 39 ですかぁ・・・

だんだん心苦しくなってくるんですが、以下を実行するとどんなメッセージがでますか？

Sub 確認5()
　　Dim a As Range
　　Dim r As Range
　　Dim c As Range
　　Dim f As Range
　　Dim x As Long

　　 '使用領域の K列から最終列までの列数
　　 x = Range("A1", ActiveSheet.UsedRange).Columns.Count - 10
　　'判定対象領域
　　 Set a = Range("I4", Range("I" & Rows.Count).End(xlUp)).Offset(, 2).Resize(, x)

　　 Set c = a.Find(What:="NA", LookAt:=xlWhole, LookIn:=xlFormulas)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If

　　 Set f = c

　　 Do
　　　　If c.Value <> c.Text Then
　　　　　　If r Is Nothing Then
　　　　　　　　Set r = c
　　　　　　Else
　　　　　　　　Set r = Union(r, c)
　　　　　　End If
　　　　End If
　　　　Set c = a.FindNext(c)
　　Loop While c.Address <> f.Address

　　If Not r Is Nothing Then
　　　　MsgBox "以下のセルを確認してください" & vbLf & Replace(r.Address, ",", vbLf)
　　Else
　　　　MsgBox "NA セルの見た目と実際の値に違いはありませんでした"
　　End If

End Sub
【78635】Re:特定の文字列の入っている行を削除し...
発言  γ  - 16/12/2(金) 22:11 -
引用なし
パスワード
   横から失礼します。
検索機能に「すべて検索」というのがありますね。
これで"NA"を検索してみてください。
思わぬ所に "NA"が潜んでいませんか？
【78636】Re:特定の文字列の入っている行を削除し...
発言  佳  - 16/12/2(金) 22:12 -
引用なし
パスワード
   こんにちは。

魔の10行がどの行か、 piropiroさんには分かっているのですよね。
ならば、どのセルに問題があるか特定して
そのセルを徹底的に調べてはいかがですか？

セルの特定はたとえばこんな風に。
Dim lastrow As Long
Dim i As Long, j As Long
Dim D as Long

D = 100 '適宜 魔の行番号を指定
'lastrow = Cells(Rows.Count, 9).End(xlUp).Row
For i = D To D
　　 For j = 11 To 7000
　　　　 If Cells(i, j) = "NA" Then
　　　　　　 Debug.Print Cells(i, j).Address　　　　　　
　　　　　　 Rows(i).Delete
　　　　 End If
　　 Next j
Next i
【78645】Re:特定の文字列の入っている行を削除し...
質問  piropiro  - 16/12/3(土) 19:03 -
引用なし
パスワード
   ▼βさん、γさん、佳 さん：

皆さま、色々と考えてくださり本当にありがとうございます。

コード早速試したいのですが会社のPCにしかファイルがなく
月曜日まで試せないのです。

月曜日確認後、またご報告させて頂きます。
よろしくお願いいたします。m( __ __ )m
【78650】Re:特定の文字列の入っている行を削除し...
質問  piropiro  - 16/12/5(月) 11:17 -
引用なし
パスワード
   ▼β さん：

こんにちは！

やはり49行が正しいのですね。

今、確認5を実行しました。
「NA セルの見た目と実際の値に違いはありませんでした」
のメッセージが出ました。
【78651】Re:特定の文字列の入っている行を削除し...
発言  β  - 16/12/5(月) 11:32 -
引用なし
パスワード
   ▼piropiro さん：

>やはり49行が正しいのですね。

私はそう思っているんですが、でも　確認3 と 確認4 いずれも 39 だったんですよね？
であれば、正しいのは 39 ということになります。

かつ、確認5 で　違いはない　というメッセージがでたとなると、
私が思っていたことが原因ではなかったということになります。

正しいのが 39 なのに、なぜ 49 対象になってしまうのか？

謎が深まるばかりです。

念のためですけど、このシートには結合セルはありますか？
【78652】Re:特定の文字列の入っている行を削除し...
質問  piropiro  - 16/12/5(月) 11:35 -
引用なし
パスワード
   ▼γ さん：

こんにちは！

アドバイスありがとうございます。

>検索機能に「すべて検索」というのがありますね。
>これで"NA"を検索してみてください。

やってみましたが、やはり39行でした。。。
【78653】Re:特定の文字列の入っている行を削除し...
質問  piropiro  - 16/12/5(月) 11:39 -
引用なし
パスワード
   ▼β さん：

早速のご連絡ありがとうございます！
こんなに調べて頂いているのに本当に申し訳ないです＞＜

>念のためですけど、このシートには結合セルはありますか？

CSVファイルなので結合セルはないです。
【78654】Re:特定の文字列の入っている行を削除し...
発言  β  - 16/12/5(月) 14:01 -
引用なし
パスワード
   ▼piropiro さん：

本当の原因はわかりませんが、そちらで試してもらった確認3,確認4,確認5 の結果から
以下のコードで、正しく【39行のみ】削除されるはずです。

Sub Test()
　　Dim a As Range
　　Dim r As Range
　　Dim c As Range
　　Dim f As Range
　　Dim x As Long

　　 '使用領域の K列から最終列までの列数
　　 x = Range("A1", ActiveSheet.UsedRange).Columns.Count - 10
　　'判定対象領域
　　 Set a = Range("I4", Range("I" & Rows.Count).End(xlUp)).Offset(, 2).Resize(, x)

　　 Set c = a.Find(What:="NA", LookAt:=xlWhole, LookIn:=xlFormulas)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If

　　 Set f = c

　　 Do
　　　　If r Is Nothing Then
　　　　　　Set r = c
　　　　Else
　　　　　　Set r = Union(r, c)
　　　　End If
　　　　Set c = a.FindNext(c)
　　Loop While c.Address <> f.Address

　　r.EntireRow.Delete

End Sub
【78655】Re:特定の文字列の入っている行を削除し...
質問  piropiro  - 16/12/5(月) 14:17 -
引用なし
パスワード
   ▼β さん：

何度も何度もありがとうございます！
本当にごめんなさいって感じです＞＜

試しました。
「重複する選択範囲に対してそのコマンドを使用することはできません。」
　っと言われました「r.EntireRow.Delete」のところでです！
【78656】Re:特定の文字列の入っている行を削除し...
発言  β  - 16/12/5(月) 17:45 -
引用なし
パスワード
   ▼piropiro さん：

ごめんなさい。ある行に複数NAがあって、その中のどれかの上下にも連続したNAがあるんですね。
コードを少し変更しました。

Sub Test()
　　Dim a As Range
　　Dim r As Range
　　Dim c As Range
　　Dim f As Range
　　Dim x As Long

　　 '使用領域の K列から最終列までの列数
　　 x = Range("A1", ActiveSheet.UsedRange).Columns.Count - 10
　　'判定対象領域
　　 Set a = Range("I4", Range("I" & Rows.Count).End(xlUp)).Offset(, 2).Resize(, x)

　　 Set c = a.Find(What:="NA", LookAt:=xlWhole, LookIn:=xlFormulas)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If

　　 Set f = c

　　 Do
　　　　If r Is Nothing Then
　　　　　　Set r = c.EntireRow
　　　　Else
　　　　　　Set r = Union(r, c.EntireRow)
　　　　End If
　　　　Set c = a.FindNext(c)
　　Loop While c.Address <> f.Address
　　
　　r.Delete

End Sub
【78657】Re:特定の文字列の入っている行を削除し...
お礼  piropiro  - 16/12/5(月) 19:01 -
引用なし
パスワード
   ▼β さん：

>ごめんなさい。ある行に複数NAがあって、その中のどれかの上下にも連続したNAがあるんですね。
>コードを少し変更しました。

すみません。その通りです。説明不足で2度手間になってしまって
こちらこそ申し訳ありません＞＜

そして！すごい！！！
手動の結果と全く同じです！
こんなに丁寧に教えてくださって本当に感謝してます。
ありがとうございます。

でも実はこのコードを実行する前に
任意のフォルダを選択→そのフォルダ内のファイルをすべて結合
→重複削除
を実行してからβさんのコードを実行するのですが
すべて通してやると、また49行が削除されてしまうのです。

でも、今回の質問に関しては解決していただいたので
ここでCLOSEさせて頂きます。

どうしてもまたわからなかったら再度質問させて頂きます。
本当にお世話になりました。ありがとうございます！
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
735 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free