《エクセル2000》非表示の行を削除したい

質問

1シートあたり、非表示にされている部分が3～4箇所あるシートが40枚ほどあります。

その非表示部分だけを削除したいのですが、早く作業できるいい方法はありますか？

マクロは使えなくはないですが、初心者レベルです。
よろしくお願いします。

※「非表示部分以外を選択し、別シートにコピー」というのはなしで。試してみたのですが、複雑に書かれている矢印がたくさんあり、その形が崩れてしまいました。

※逆の発想で「非表示のセルに色をつける」という方法でも結構です。（色がついていれば間違えずに手作業で削除できますので）

回答No.7

KenKen_SP · ベストアンサー

Wendy02さん、ご考慮いただけてありがとうございます。

実はもうひとつありまして、UsedRange では必ずしも正しい処理範囲を取得できないのです。セルのデータより下にシェープがある場合ですね。この場合、シート内のシェープを全て調べてまわらないと正しい最下行は取得できません。

勝手ながらWendy02さんのコードにそのあたりを盛り込んでみました。m(__)m

これで、質問主さまのご希望の動作は、マクロを1回実行するだけで完了するはずですよ。

Sub HiddenRow_Delete3()

　　Dim i As Long, j As Long, x As Long
　　Dim Sh As Worksheet
　　Dim Shp As Object
　　Dim LastRow As Long, tempRow As Long
　　Dim BufR() As Long

　　Application.ScreenUpdating = False
　　For i = 1 To ThisWorkbook.Sheets.Count
　　　　'処理対象シート
　　　　Set Sh = Sheets(i)
　　　　x = 0
　　　　For Each Shp In Sh.DrawingObjects
　　　　　　With Shp
　　　　　　　　'一応サイズが変わらないように
　　　　　　　　.Placement = xlMove
　　　　　　　　'シェープの右下セルの行番号退避
　　　　　　　　ReDim Preserve BufR(x)
　　　　　　　　BufR(x) = .BottomRightCell.Row
　　　　　　End With
　　　　Next Shp
　　　　'最終セル取得
　　　　On Error Resume Next
　　　　　　LastRow = Application.WorksheetFunction.Max(BufR)
　　　　On Error GoTo 0
　　　　With Sh.UsedRange ' 一応 UsedRange と比較しておく
　　　　　　tempRow = .Row + .Rows.Count - 1
　　　　End With
　　　　If LastRow < tempRow Then LastRow = tempRow
　　　　'行ごと削除
　　　　For j = LastRow To 1 Step -1
　　　　　　If Sh.Rows(j).EntireRow.Hidden = True Then
　　　　　　　 Sh.Rows(j).EntireRow.Delete Shift:=xlShiftUp
　　　　　　End If
　　　　Next j
　　Next i
　　Application.ScreenUpdating = True
　　Set Sh = Nothing

End Sub

回答No.10

KenKen_SP

＞非表示の行が、UsedRangeに関知しない状況が起こりうるものなのでしょうか

本件に限っては起こり得ます。例えば、

・A1:C10までデータが入力されている
・シェープはA1:C30のセル上に配置されている
・20～23行目が非表示となっているが、この行のセルにデータはなし

この状況で　ActiveSheet.UsedRange.Select とすると　A1:C10の範囲しか取得できませんので、20～23行目は処理対象外となってしまいます。本件ではセルデータがある範囲ではなく、シェープの配置された範囲を処理対象とすべきなのかなと思うのですが、そうなるとUsedRange では取得できなかったので、投稿させていただきました。悪意はありません。互いにアイディアを出し合って、ベストな解が導ければ良いなぁ、、といった軽い気持ちでした。

ご質問主さま、Wendy02さま、非礼申し訳ありませんでした。

お礼

こんばんは。

今回、No.7のご回答で、無事作業を終了することが出来ました。
少しずつ、私も覚えていきたいと思います。
本当にありがとうございました！

補足

■回答してくださった皆様へ■（3/19）
こちらの補足欄をお借りして、一言お礼を申し上げます。
今日こちらの質問を確認して、あまりにもたくさんの書き込みをいただき、驚いております。
ただ折しも連休に突入してしまったため、会社のPCで試してみることができません。
もしかしたら、まだ書き込みたい方がいらっしゃるかもしれませんし、個々の方へのお礼と締め切りは来週にさせていただきます。

回答No.9

Wendy02

KenKen_SPさまへ
＞実はもうひとつありまして、UsedRange では必ずしも正しい処理範囲を取得できないのです。セルのデータより下にシェープがある場合ですね。この場合、シート内のシェープを全て調べてまわらないと

私は、おっしゃることには疑問を感じています。
UsedRange は、オートシェイプやオブジェクトに目を行けば、確かに、UsedRange では、ワークシート上のオブジェクトには関知しませんが、ここで問題になるのは、非表示の行なのですね。

ともかく、非表示の行が、UsedRangeに関知しない状況が起こりうるものなのでしょうか？
こちらがテストした段階では、一旦、Applicationを終了してから後に、もう一度、開いて、シートのUsedRangeの範囲を取りましたが、非表示の行までの範囲を取っています。

もちろん、心配なら、1列だけを、１行目から65536行目までの、非表示行を調べたとしても、それほどに時間は取らないでしょうけれど。

一応、こちらへのマクロの変更については、質問者不在のままに進めるのも気が進みませんので、これまでにします。どなたでも、完成品と思われるものを、掲示していただければ、私としては幸いです。

quilt123さまへ
>今回の場合ワークシートに限定した方がベターではないでしょうか
ご指摘のとおりです。ご指摘、ありがとうございました。

お礼

Wendy02さま、度重なるご回答をありがとうございました。

お陰様で、あっという間に作業が終了し、驚くやら嬉しいやら。
これも、回答して下さった皆様のおかげです。
本当にありがとうございました。

回答No.8

quit123

Wendy02さん、KenKen_SPさん、こんばんは。
コードの提示、及び改良コードありがとうございます。
もうひとつ、宜しいでしょうか。

お二人のコードの次の部分

For i = 1 To ThisWorkbook.Sheets.Count　（お二人）
　　With Sheets(i).UsedRange　（Wendy02さん）
　　Set Sh = Sheets(i)　（KenKen_SPさん）

は、ワークシート以外のシートがあればエラーになりますので
今回の場合ワークシートに限定した方がベターではないでしょうか

For i = 1 To ThisWorkbook.WorkSheets.Count
　　With WorkSheets(i).UsedRange
　　Set Sh = WorkSheets(i)

ーーーーーーーーーーーーーーーーーーーーーーーー

それから、KenKen_SPさんにお尋ねします。

＞セルのデータより下にシェープはUsedRange では
＞必ずしも正しい処理範囲を取得できない、

と言っておられますが、これ初耳でしたので
ちょっと検証してみましたが、上手く範囲取得されます
後学のためも少し詳しく説明いただければと思います

当方コートの提示もせず粗探しみたいになってますが
少しでも質問者のためになればとの考えからですので
ご無礼お許しください。
お二人のコードとても為になります。
これからも素晴らしい回答楽しみにしています。

お礼

quit123さま、こんばんは。

…正直申し上げまして、皆様の議論されていることは、ビギナーには理解するに厳しい内容となっております(^^;)

そんな達人の皆様のおかげで、無事作業を終了することができました。
本当にありがとうございました。

回答No.6

Wendy02

皆さん、アドバイスありがとうございます。
ワークシートの操作はあまり慣れませんので、分かりませんでした。おっしゃることを、確認しました。これではどうでしょうか？

Sub HiddenRow_Delete2()
　Dim i As Long
　Dim j As Long
　Application.ScreenUpdating = False
　For i = 1 To ThisWorkbook.Sheets.Count
　　With Sheets(i).UsedRange
　　　For j = .Rows.Count To 1 Step -1
　　　　If .Rows(j).EntireRow.Hidden = True Then
　　　　　.Rows(j).EntireRow.Delete Shift:=xlShiftUp
　　　　End If
　　　Next
　　End With
　Next
　Application.ScreenUpdating = True
End Sub

回答No.5

KenKen_SP

quit123さんが指摘されておりますが、#2Wendy02さんのコードにおいて

For Each r In .Resize(, 1)
　If r.EntireRow.Hidden = True Then
　　r.EntireRow.Delete Shift:=xlShiftUp
　End If
Next

だと、処理がうまくいきません。例えば、1行目を削除すると、2行目が1行目にシフトアップするわけですが、次の2行目は元の3行目になってしまいます。このままでは、元の2行目がいつまでも処理されないことになります。

このような場合、最終行から開始行に向かって処理していくと良いと思います。

for i=最終行番号 to 開始行版行 step -1

ご参考いただけると幸いです。

回答No.4

quit123

No2のWendy02さんへ

最近よく回答されてますね。
色々勉強になります。

提示のコードをちょと眺めてみましたが
ある条件のとき上手くいかないですよね。(^^;;;

回答No.3

chirop

私はいつも下記の方法でやっています。
（矢印が崩れる可能性はあるかもしれませんので、No.1の方法で固定するといいと思います。）

(1)空いている列の可視セルだけを選択し、「１」等のフラグを立てる。
（該当列の一番上の行に1を入力し、コピー。⇒その下から最下行までを選択し、編集／ジャンプ／セル選択／可視セルにチェックし、貼付）

(2)全体にオートフィルタをかけて、該当列で「空白セル」を選択し、可視セルのみ行ごと削除。

お礼

こんばんは。

こちらの方法でも、大丈夫ですね。
今回はマクロを使わせていただきましたが、是非今後の参考にさせていただきます。
どうもありがとうございました。

回答No.2

Wendy02

設定の細かい説明はしませんが、標準モジュールに貼り付けると良いと思います。
これは、同じブックの全てのシートに対して、実行します。

Sub HiddenRow_Delete()
　Dim r As Range
　Application.ScreenUpdating = False
　For i = 1 To ThisWorkbook.Sheets.Count
　　With Sheets(i).UsedRange
　　　For Each r In .Resize(, 1)
　　　　If r.EntireRow.Hidden = True Then
　　　　　r.EntireRow.Delete Shift:=xlShiftUp
　　　　End If
　　　Next
　　End With
　Next
　Application.ScreenUpdating = True
End Sub

＞※逆の発想で「非表示のセルに色をつける」という方法でも結構です。（色がついていれば間違えずに手作業で削除できますので）

こちらは、面倒なので、割愛します。

回答No.1

KenKen_SP

＞※「非表示部分以外を選択し、別シートにコピー」というのはなしで。試してみたのですが、複雑に書かれている矢印がたくさんあり、その形が崩れてしまいました。

とありますが、その「矢印」を全部選択し、

[オートシェイプの書式設定]-[プロパティ]で[セルに合わせて移動するがサイズ変更はしない]にしてから、

別シートにコピーしたらいけると思いますよ。

お礼

なるほど。
ただ今回、作業量がちょっと膨大になってしまうので、マクロで削除という方法をとらせていただきます。

こちらの方法、少しの作業ですむ場合は、ぜひ活用させて下さい。
どうもありがとうございました。