非表示の行の保護方法を教えてください。

Question

VBA初学者です。
回答よろしくお願い致します。

#説明
（１）シートの
３～５行目
１０～１５行目
３４～５０行目
・
・
・
と、いくつもの行が非表示になっています。

（２）この非表示の行のみ、保護し、データを入力できないようにしたいです

（３）なので、以下のコードを入力すればよいと思います。

【全ての非表示の行】.Locked = True
ActiveSheet.Protect AllowFormattingCells:=True

#質問
**【全ての非表示の行】**は、どのようにコードで表せば よいでしょうか。
※非表示の行は、人の操作で、様々に変わっていくので、その行を自動で認識できる方法を知りたいです。

よろしくお願い致します。

Answer 1

この非表示の行のみ、保護し、データを入力できないようにしたいです

非表示の行を別にシートに分けるという発想なら対応できなくもないです。
詳しくはこちらのページをご覧ください。

【全ての非表示の行】は、どのようにコードで表せば よいでしょうか。

※非表示の行は、人の操作で、様々に変わっていくので、その行を自動で認識できる方法を知りたいです。

グレーアウトというセルの色で識別する方法なら私も何度かやったことがあります。
通常では滅多に使わない色を指定しておくことで、仕様ということにもなるので凄く便利です。
（別にシートに分ける方法ならそんな判定すら必要ありませんが...）

Comments

[ta-]

回答ありがとうございます。
グレースケールの色付けセルを検索して、そのセルをロック・保護という意味でしょうか。
確かにそれならば、いけそうな気がします。

一度試してみます。

[Zuishin]

もう一つの回答は重いから却下なのに、こちらはいいんですか？
計測してこちらの方が速かったということでしょうか？

質問の主題に合っているのはもう一つの回答の方だと思いますが、それを却下してこちらを採用した理由を教えてください。

[ta-]

計測方法などわからないので、私の経験からですが、回答します。

説明A
（１）全セルを選択します
（２）Hiddenプロパティで、以下のようにします。
　　　１列目表示、２~５列目非表示
　　　６列目表示、７~１０列目非表示
　　１１列目表示、１２~１５列目非表示
　　　・
　　　・
　　　・
（３）全セルを（２）のようにFOR　NEXTで実施しようとするとエクセルがフリーズしました。

説明B
（１）全セルのうち、数式と条件付き書式のみを選択し、保護・ロックをしてもフリーズせず、スムーズに遂行できました。

よって
説明Aより、Hiddenプロパティを全セルにしようするとフリーズするのではないかと思いました。

また説明Bの要領で
『グレースケールというセルの色』を選択し、保護・ロックをかければ、全セル選択しても、フリースせずに、実施できるのではないかと思ったのが理由です。

[ta-]

説明Aのコード
Sub 選択行を非表示()

Dim my非行 As Long
my非行 = 5

Dim my行始め As Long
my行始め = Selection.Row
Dim my行終わり
my行終わり = Selection.Rows.Count + my行始め - 1
Dim my行終わり2 As Long
my行終わり2 = (Selection.Rows.Count \ my非行) * my非行 + my行始め - 1

Rows(my行始め & ":" & my行終わり).Hidden = False

Dim i As Long
For i = my行始め To my行終わり2 Step my非行

Rows(i + 1 & ":" & i + my非行 - 1).Hidden = True

Next i

End Sub

[Zuishin]

全セルをグレーにしてもフリーズしなかったんですか？

[ta-]

説明Bのコード
Sub 数式と条件書式保護()

Call [あ1保護解除]
Call [あ2全セル透明色]
Call [あ5数式色付け]
Call [あ6条件書式色付け]
Call [あ7全セルロック解除]
Call [あ10数式ロック]
Call [あ11条件書式ロック]
Call [あ12全セル保護]
End Sub

Sub あ1保護解除()

ActiveSheet.Unprotect
End Sub

Sub あ2全セル透明色()

Cells.Interior.Color = xlNone
End Sub

Sub あ5数式色付け()

Cells.SpecialCells(xlCellTypeFormulas).Interior.Color _
= RGB(160, 230, 170)
End Sub

Sub あ6条件書式色付け()

Cells.SpecialCells(xlCellTypeAllFormatConditions).Interior.Color _
= RGB(160, 230, 170)
End Sub

Sub あ7全セルロック解除()

Cells.Locked = False
End Sub

Sub あ10数式ロック()

Cells.SpecialCells(xlCellTypeFormulas).Locked = True
End Sub

Sub あ11条件書式ロック()

Cells.SpecialCells(xlCellTypeAllFormatConditions).Locked = True
End Sub

Sub あ12全セル保護()

ActiveSheet.Protect AllowFormattingCells:=True
End Sub

[ta-]

説明Bのコードより、フリーズしないのではないかと思います。

[Zuishin]

> Cells.SpecialCells(xlCellTypeFormulas).Interior.Color _
= RGB(160, 230, 170)

こういうのを RGB ではなく Hidden で非表示にするのではだめなんですか？

[ta-]

ちなみに以下のコードでもフリーズしないです。
Sub 全セル保護()

Call [あ1保護解除]
Call [あ3全セル色付け]
Call [あ5数式色付け]
Call [あ6条件書式色付け]
Call [あ8全セルロック]
Call [あ12全セル保護]
End Sub

Sub あ1保護解除()

ActiveSheet.Unprotect
End Sub

Sub あ3全セル色付け()

Cells.Interior.Color = RGB(215, 215, 215)
End Sub

Sub あ5数式色付け()

Cells.SpecialCells(xlCellTypeFormulas).Interior.Color _
= RGB(160, 230, 170)
End Sub

Sub あ6条件書式色付け()

Cells.SpecialCells(xlCellTypeAllFormatConditions).Interior.Color _
= RGB(160, 230, 170)
End Sub

Sub あ8全セルロック()

Cells.Locked = True
End Sub

Sub あ12全セル保護()

ActiveSheet.Protect AllowFormattingCells:=True
End Sub

[Zuishin]

色を付ける意味がわからない。
結局数式のあるところをロックしてるだけですよね？
色はどういう役割を果たしているんですか？

[ta-]

データや関数を構築していく際に
間違わないように、つけているだけです

[ta-]

Cells.SpecialCells(xlCellTypeFormulas).Interior.Color _
= RGB(160, 230, 170)

こういうのを RGB ではなく Hidden で非表示にするのではだめなんですか？

→→Hiddenプロパティは、行や列全体を選択します。
　　Cells.SpecialCells(xlCellTypeFormulas).Interior.Color _
= RGB(160, 230, 170)は、セルを選択するので

選択している種類が違うので、不可能と思います。

[Zuishin]

でも行や列毎にロックするんですよね？
だったらどっちでも同じじゃ？

[ta-]

確認しました。
不可能でした。

Sub test2()

Cells.SpecialCells(xlCellTypeFormulas).Hidden = True

End Sub

エラーメッセージ↓
Rangeクラスの、HIddenプロパティを設定できません。

[Zuishin]

いや、SpecialCells から行番号を抽出してユニークして。
まあでも色の意味はわかりました。
ありがとうございました。

[stdio]

名前間違ってました。グレースケールではなくグレーアウトですね。回答の方も編集しておきました。
エクセルでセルの背景をグレーにすると編集済みと思われる効果があります。

[ta-]

ありがとうございます。
また確認してみます！

Answer 2

全てまとめて…とは行きませんが
hiddenプロパティで非表示になっているかどうか1行ずつ判別していってはどうでしょう？

'非表示行だった場合その行をロックする
If Rows(行番号).Hidden = True Then 
    Rows(行番号).Locked = True
End If

Comments

[ta-]

回答ありがとうございます。
全てのセルを１行づつ行うと、処理量が大きく、PCへの負担が気になってしまいます（汗

[stdio]

遅い…重い…そんなエクセルVBAプログラムの処理速度を劇的に改善する方法があるので、実施してみるといいかも知れません。プログラムが書かれているのはページの最後の方「まとめとして最終的なプログラムを紹介しておきますね。」と書かれた後のプログラムです。
https://tonari-it.com/vba-processing-speed/

[ta-]

回答ありがとうございます。
初学者なので、色々と挑戦するのは、また後にしてみます。

まずは、グレースケールの方を行ってみます。