Excel にて非表示行を探すワークシート関数

質問

ある膨大なデータをまとめた数百行からなる Excel ファイルがある
のですが、その中で数箇所「非表示にされた行」があります。
その非表示行を探すワークシート関数って有りますでしょうか？
(ワークシート関数が無理なら、マクロでも良いです。)

・非表示にされた行は１行だけの時もありますし、
　2, 3 行をまとめて非表示にしている場合もあります。
・Excel バージョンは 2003 です。
・私の方で見つけた１つの方法として、以下の方法があります。
　　1. 適当な列に連続データで 1 から始まる番号を振る
　　2. それを適当なグラフにする
　　3. グラフの中で急に傾きが変化している所があれば、そこが非表示行である
・ただし、上記の方法ですと数十行なら視覚的に見つけやすいのですが
　数百行、数千行の場合はグラフの中の傾きの変化が見つけづらいので NG
・一番理想的なのは画像の中の B4 (黄色いセル) のように、非表示行の１つ上の行に
　"1" を表示し、かつ、それをワークシート関数で実現する事です。
・Ctrl-A で全部のセルを選択し、適当な所で右クリックをして「再表示」とすれば
　非表示行が表示される方法は既に知っております。
　私が知りたいのは「非表示の状態で、非表示行の場所」を知りたいのです。

以上、よろしくお願いします。

回答No.1

mar00 · ベストアンサー

関数ではなくてマクロを使った方法ですが
Sub Macro1()
Dim i As Long
Dim MaxRow As Long
With ActiveSheet.UsedRange
MaxRow = .Rows(.Rows.Count).Row
End With
For i = 1 To MaxRow
If Rows(i + 1).EntireRow.Hidden = True Then
If Rows(i).EntireRow.Hidden = False Then
Range("B" & i) = 1
Range("B" & i).Interior.ColorIndex = 36
End If
Else
Range("B" & i) = 0
End If
Next i
End Sub
ご希望通りであればいいのですが。

お礼

返信が遅れてすみません。
バッチリ希望通りの動作です。
ありがとうございました。本当に助かりました。
これで数百行の Excel ファイルで、非表示行の存在を気にしつつ
上から下までデータを閲覧する事ができます。
効率的に見る事ができるようになり助かりました。

回答No.5

keithin

ご利用のエクセルのバージョンが不明ですが，Excel2003以降で。

Ａ列に「何か」埋めておきます。数字でも文字でもナンデモ構いません。
Ｂ１に
=IF(SUBTOTAL(103,A1),"",ROW(A1))
と記入し，下向けにオートフィルドラッグでリスト下端まで埋めておきます。

（隠れてない行の範囲で）
たとえばＤ１に
=SMALL(B:B,ROW(D1))
と記入して下向けにコピーすると，「隠れた行の行番号」が並びます。

お礼

返信が遅れてすみません。
SUBTOTAL 103 , ROW , SMALL を駆使して非表示行を調べるのですね、
keithin さんの方法でも非表示行のリストアップができる事を確認しました。
マクロを使わないのであれば、こう言う方法もあるのですね、
参考になりました。ありがとうございました。

回答No.4

imogasi

エクセルの関数のできる対象をはっきり掴んでないから、無駄な質問がよく出る。
エクセルの関数は、歴史的経緯からか、対象はセルの値だけに限られる。返す値もセルのだけ値だ。
セルの属性的なものには、本件の非表示や、列幅、行高、表示形式、数式、コメントなどは対象から外れるもの（関数では扱えない。そういうものには手を広げない設計・体系になっている）。
VBAであれば１シートの普通の範囲（行数、数千行）であれば、VBAで実行して実用になるだろう。
しかしそういうブックが多数あると、実行時間がかかって使えないかも知れない。
＞ある膨大なデータをまとめた数百行からなる
膨大なデータは、素（基）のデータなのか、素（基）のデータをまとめたデータのほうか、はっきりしない大雑把な書き方だ。
質問振りから、こういうことを考えるのは専門家に相談すると良い。VBAの場合は丸投げになるが、意味ややり方は判らないだろうから。
ーー
非表示の行がグラフに要素として表示されないことを使うなど、邪道だと思う。よければどうぞ。
ーー
マクロの記録をも勉強した形跡はない。それを少し変えて、
Sub test01()
For i = 1 To 10
If Rows(i).EntireRow.Hidden = True Then
MsgBox i
End If
Next i
End Sub
で臭いはわかるだろう。非表示行の行番号がつかめる。
ーーーー
参考に「エクセル　VBA　非表示行　探索」
で照会すること。
ーー
それより、表示の場所を知って何をしたいか書いておくと、初心者的な発想かどうかわかる場合が在る。
質問するときは、（自明な場合以外は）その後の目的も一応書け。

回答No.3

tom04

No.2です！
たびたびごめんなさい。

色を付けるセルはB列でしたね！前回はA列に色を付けてしまいました。
もう一度訂正したコードを載せておきます。

Sub test()
Dim i As Long
With Columns(2)
.Interior.ColorIndex = xlNone
.ClearContents
End With
Application.ScreenUpdating = False
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Rows(i).Hidden = True Then
With Cells(i - 1, 2)
.Value = 1
.Interior.ColorIndex = 6
End With
End If
Next i
Application.ScreenUpdating = True
End Sub

何度も失礼しました。m(_ _)m

お礼

返信が遅れてすみません。
２度にわたり回答して頂いてありがとうございました。
tom04 さんのマクロもほぼ思った通りに動いてくれる事を確認しました。
Rows(i).Hidden で非表示かどうかの判断ができるんですね、
参考にさせて頂きます。

回答No.2

tom04

こんばんは！
VBAになってしまいますが・・・
一例です。

操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i As Long
Columns(1).Interior.ColorIndex = xlNone
Columns(2).ClearContents
Application.ScreenUpdating = False
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Rows(i).Hidden = True Then
With Cells(i - 1, 1)
.Interior.ColorIndex = 6
.Offset(, 1) = 1
End With
End If
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

※　行を再表示した場合は、非表示だった行にも色がついています。（B列にも「1」が表示）
※　もう一度マクロを実行すると色は消え、B列の「1」もクリアされます。

こんな感じではどうでしょうか？m(_ _)m