[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『空白行の削除について』(ぴーしゃん)
分かる方がいれば教えてください。
空白行の削除をやりたいのですが、
一般的な考え方だと
↓
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つ値があった場合には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 変数を1つ減らしました)
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
数式自体は関数をいれています。
A B C D
1 あ|い|う|え| 下の行が上の文字を参照するというものです。
2 | | | | 見た目は隠れていますが、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
とりあえずこれで数式の入っていない、I列が空白の行は削除されると思います。 参照エラー値のある行はそのまま残ります。 エラー値のある行も削除する場合は
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.