[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『空白行の削除について』（ぴーしゃん）
分かる方がいれば教えてください。
空白行の削除をやりたいのですが、
一般的な考え方だと
↓
Sub 空白行を削除する()
Dim row_end As Long Dim msg As String Dim i As Long
msg = "空白の行をすべて削除しますか？"
If MsgBox(msg, vbYesNo) = vbNo Then Exit Sub
row_end = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = row_end To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
Rows(i).Delete
End If
Next i
End Sub
こんな感じかと思います。
これは非表示列があったとしても1行すべてが空白であればもちろん削除されますよね。
そこで本題の質問なのですが、
たとえ表示された列が空白行でも、非表示列に１つ値があった場合には1行すべてが空白でないため削除されないですよね？
数式セルが多いため非表示ので列を削除に変えてしまうと処理時間が長いので解決できるコードがあれば教えていただきたいです・・・
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
非表示列の値は無視して見た目空白行なら削除？
それとも、見た目空白でも非表示列に値があれば削除したくない？（完全な空白行のみ削除）
それから、例えば特定の列のセルを見て、
その行が削除する行かどうか判断できるならかなり
コードが簡単になるんですが。。。
（まっつわん） 2017/03/23(木) 19:40
分かりにくくてすみません。
こっちです
↓
非表示列の値は無視して見た目空白行なら削除
＞それから、例えば特定の列のセルを見て、
その行が削除する行かどうか判断できるならかなり
コードが簡単になるんですが。。。
特定列ではないです。変動します。
（ぴーしゃん） 2017/03/23(木) 19:45
もう少しスマートな記述がありそうですが、眠いので ベタベタコードです。
Sub Sample()
Dim r As Range
Dim a As Range
Dim c As Range
Dim d As Range
Dim flg As Boolean
With Range("A1", ActiveSheet.UsedRange)
For Each r In .Rows
Set a = r.SpecialCells(xlCellTypeVisible)
flg = True
For Each c In a
If Not IsEmpty(c) Then
flg = False
Exit For
End If
Next
If flg Then
If d Is Nothing Then
Set d = r
Else
Set d = Union(d, r)
End If
End If
Next
End With
If Not d Is Nothing Then d.Delete
End Sub
（ β） 2017/03/24(金) 00:54
やはりねぼけてましたね。以下でよかったです。
(8:43 変数を１つ減らしました）
Sub Sample2()
Dim r As Range
Dim d As Range
With Range("A1", ActiveSheet.UsedRange)
For Each r In .Rows
If WorksheetFunction.CountA(r.SpecialCells(xlCellTypeVisible)) = 0 Then
If d Is Nothing Then
Set d = r
Else
Set d = Union(d, r)
End If
End If
Next
End With
If Not d Is Nothing Then d.Delete
End Sub
（ β） 2017/03/24(金) 07:29
そちらのオリジナルコードのほう式のままで手を加えれば以下のようになります。 xlCellTypeLastCell は 『まぎれ』があって、ちょっと不安ですが、そのままにしてあります。 （現在は、すべてのバージョンで UsedRange のまぎれは解消されているようですので UsedRangeの右下隅のセルの行番号を使うほうが安心です）
Sub 空白行を削除する()
Dim row_end As Long
Dim msg As String
Dim i As Long
msg = "空白の行をすべて削除しますか？"
If MsgBox(msg, vbYesNo) = vbNo Then Exit Sub
row_end = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = row_end To 1 Step -1
If WorksheetFunction.CountA(Rows(i).SpecialCells(xlCellTypeVisible)) = 0 Then
Rows(i).Delete
End If
Next i
End Sub
（β） 2017/03/24(金) 08:54
3つも投稿ありがとうございます！
勉強もまだまだなので、じっくりコードの内容を理解したいと思います。
ありがとうございましたm(__)m
（ぴーしゃん） 2017/03/24(金) 09:19
自分でもちょこちょこデータをいじったのですが
上手くいってくれません…＞＜；
5行目〜最終行までを範囲にしたいです。
ちなみになのですが、I列が空白だった場合、
行を削除っていうコードをつなげることはできますか？
我が儘な質問ですみません。
↓
Sub 空白行を削除する()
Dim row_end As Long
Dim msg As String
Dim i As Long
Dim j As Long
Dim ws As Worksheet
For i = 2 To ThisWorkbook.Worksheets.Count
Set ws = ThisWorkbook.Worksheets(i)
msg = "空白の行をすべて削除しますか？"
If MsgBox(msg, vbYesNo) = vbNo Then Exit Sub
row_end = ws.Cells.SpecialCells(xlCellTypeLastCell).row
For j = row_end To 1 Step -1
If WorksheetFunction.CountA(ws.Rows(j).SpecialCells(xlCellTypeVisible)) = 0 Then
ws.Rows(j).Delete
End If
Next j
Next i
End Sub
（ぴーしゃん） 2017/03/24(金) 14:28
その条件だと、そもそもCellsで全部を考えなくてもI列だけ空白だったら削除 でいいんじゃないですか？ だって、1行全部が空白ってことはI列も空白ということですよね？
あと、メッセージボックスはForの前に持ってきたほうがいいと思いますよ。 For i の回数分だけ削除するかどうか聞かれてしまいます。 （2Win） 2017/03/24(金) 15:01
On Error Resume Next
Worksheets(1).Columns("I").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'ワークシートの1番目の列のIの空白セルの行全体を削除
On Error GoTo 0
End Sub
結局特定の列（I列）を見れば削除できるか判断出来るってことですかね？
（まっつわん） 2017/03/24(金) 15:34
＞ その条件だと、そもそもCellsで全部を考えなくてもI列だけ空白だったら削除 でいいんじゃないですか？
だって、1行全部が空白ってことはI列も空白ということですよね？
…仰る通りです。消したい列、I列全て空白でした。
＞あと、メッセージボックスはForの前に持ってきたほうがいいと思いますよ。
For i の回数分だけ削除するかどうか聞かれてしまいます。
こちらも仰る通りです…。
（ぴーしゃん） 2017/03/24(金) 15:46
I列（5行目〜最終行）を見て、空白だったら削除ということです！
（ぴーしゃん） 2017/03/24(金) 15:52
掲示のコードを書き直してみました。
Sub 空白行を削除する()
Dim row_end As Long
Dim msg As String
Dim i As Long
Dim j As Long
Dim ws As Worksheet
msg = "空白の行をすべて削除しますか？"
If MsgBox(msg, vbYesNo) = vbNo Then Exit Sub
For i = 2 To ThisWorkbook.Worksheets.Count
Set ws = ThisWorkbook.Worksheets(i)
row_end = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
For j = row_end to 5 step - 1
If ws.Cells(j, 9) = "" Then
ws.Rows(j).Delete
End If
Next j
Next i
End Sub
※ちょっと修正 3/24 16:22
（2Win） 2017/03/24(金) 16:15
言い忘れていたのですが…
見た目は空白なのですが、文字色を白にしている数式セルがあるんです。
そのシートで「型が一致しません」と表示されてしまうのですが
数式って文字として判別されないんでしょうか？
（ぴーしゃん） 2017/03/24(金) 16:48
いえ、文字か数式かは判別していないので関係ないと思いますが・・・。 そのシートに入っている値がどういった物なのか、コードを実行した時、コードのどこの行が黄色くなったか教えてもらえますか？
型が一致しませんというのは変数の宣言と一致しないということなので。 （2Win） 2017/03/24(金) 16:58
そうですよね…
ちなみに、全シート数式は入っていません。
10シート中、8シートには数式が入っていないので（2Win）様のコードで問題なく動作します。
しかし、数式が入っている2シートは止まってしまいます…
↓で止まりました
If ws.Cells(j, 9) = "" Then
（ぴーしゃん） 2017/03/24(金) 17:15
その文字色が白になっているセルで式がエラーになっていないか？ （ねむねむ） 2017/03/24(金) 17:18
数式自体は関数をいれています。
Ａ Ｂ Ｃ Ｄ
１ あ｜い｜う｜え｜ 下の行が上の文字を参照するというものです。
２ ｜ ｜ ｜ ｜ 見た目は隠れていますが、A2には=A1、B2には=B1、…のような。
言ってる意味分かりますかね…？
（ぴーしゃん） 2017/03/24(金) 17:23
#REFになっているところあります！
これで止まっちゃう感じでしょうか…？
（ぴーしゃん） 2017/03/24(金) 17:25
空欄で表示していたセルがエラー値を返した為に空欄ではなくなったということですかね。
そういう場合はワークシート関数で回避できるかもですが、ちょっとIfに工夫しないといけないですね。 （2Win） 2017/03/24(金) 17:35
＞ 空欄で表示していたセルがエラー値を返した為に空欄ではなくなったということですかね。
…ん〜おそらく…。
＞ そういう場合はワークシート関数のISNAで回避できるかもですが、ちょっとIfに工夫しないといけないですね。
こちらでISNAで試してみますが、差支えなければ参考にコードを教えていただけないでしょうか…？
（ぴーしゃん） 2017/03/24(金) 17:41
ISNAじゃないかもです。すみません。 本日は私もうレスできないかもしれないので、少々お時間いただけますか。 申し訳ございませんー （2Win） 2017/03/24(金) 17:43
ISNAじゃなくても、数式エラーをなくせるように努力してみます！
私も月曜日まで閲覧できなさそうなので
たっぷり時間を使っていただいて構いません！
恐れ入りますが、よろしくお願い致します！！
（ぴーしゃん） 2017/03/24(金) 17:55
遅くなりましたが、とりあえずエラー値の回避コードです
Sub 空白行を削除する()
Dim row_end As Long
Dim msg As String
Dim i As Long
Dim j As Long
Dim ws As Worksheet
msg = "空白の行をすべて削除しますか？"
If MsgBox(msg, vbYesNo) = vbNo Then Exit Sub
For i = 2 To ThisWorkbook.Worksheets.Count
Set ws = ThisWorkbook.Worksheets(i)
row_end = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
For j = row_end To 5 Step -1
If Not IsError(ws.Cells(j, 9)) Then
If Not ws.Cells(j, 9).HasFormula Then
If ws.Cells(j, 9) = "" Then
ws.Rows.Delete
End If
End If
End If
Next j
Next i
End Sub
とりあえずこれで数式の入っていない、Ｉ列が空白の行は削除されると思います。 参照エラー値のある行はそのまま残ります。 エラー値のある行も削除する場合は
Sub 空白行を削除する()
Dim row_end As Long
Dim msg As String
Dim i As Long
Dim j As Long
Dim ws As Worksheet
Dim ERange As Range
msg = "空白の行をすべて削除しますか？"
If MsgBox(msg, vbYesNo) = vbNo Then Exit Sub
For i = 2 To ThisWorkbook.Worksheets.Count
Set ws = ThisWorkbook.Worksheets(i)
row_end = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
For j = row_end To 5 Step -1
If Not IsError(ws.Cells(j, 9)) Then
If Not ws.Cells(j, 9).HasFormula Then
If ws.Cells(j, 9) = "" Then
ws.Rows.Delete
End If
End If
End If
Next j
On Error Resume Next
Set ERange = ws.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not ERange Is Nothing Then
ERange.EntireRow.Delete
End If
Next i
End Sub
これでエラーの出ている行も消えます。
色々とご自分の環境に合うように書き直してみてください。 （2Win） 2017/03/27(月) 01:24
返信が遅れてしまいました。すみません。
ざっと拝見させていただきました！
あとは自分でなんとかしてみます！
ご親切に、どうもありがとうございました＾＾
（ぴーしゃん） 2017/03/27(月) 17:44
先ほど上記で動作してみました。
ISERRORじゃないがためになのか、
非表示にしていた列が表示されてしまい、文字がある行も空白行も
全て削除されてしまいました…
For j = row_end To 5 Step -1
If Not IsError(ws.Cells(j, 9)) Then
If Not ws.Cells(j, 9).HasFormula Then
If ws.Cells(j, 9) = "" Then
ws.Rows.Delete
非表示にしていた列が表示された原因が分かりません…
ちなみに、ただの#REF!エラーの場合、
ISERROR を ERROR に変えてもダメですよね？
18:42編集
（ぴーしゃん） 2017/03/27(月) 18:26
非表示の列が表示されたのもエラーが出たからだと思います。
文字がある行も空白行も削除されたというのがいまいちどういう状況なのか分からないです。
そのまんまでなくて結構ですので、どういったものがどのシートのどのセルに配置されているのかを 記載していただけますか。 これ以上は詳細が分からないと検証できないと思いますので。
ちょっと疑問なんですが、行を削除してエラーが返ってくる状況で、本当にその行は削除していい行なんでしょうか？ 例えば、行じゃなくても参照しているセルは残して不要なセルは残すということもできると思います。 またはエラーになった場合は違う値を返す数式にしておくですとか。 （2Win） 2017/03/28(火) 09:05
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.