エクセルVBAで非表示にする方法

質問

エクセルのVBAを使って表示・非表示の切り替えをする方法を教えてください。

マクロは初心者のため、コードを書いていただけると嬉しいです。

(1)6～9行目、11～14行目、16～19行目…151～154行目まで を表示・非表示
(2)合計列・差額列 (列に名前を入れています。ABCなどどの列に入るかはケースによりかわります)

今のところ、
Rows("6:9").Hidden = True
Rows("11:14").Hidden = True
Rows("16:19").Hidden = True
.
.
.
Columns("Q:R").Hidden = True
で誤魔化していますが、時間がかかりますし、列はその都度設定している状況です。

ご指導お願いします。

回答No.6

cj_mover

＃３、５、ｃｊです。

さらにもう少しだけ軽く、扱い易いものに書き直してみました。

＃３でも触れましたが、
コマンドボタンの.TakeFocusOnClickプロパティはFalseに設定してください。
（他の方の回答コードでも同じです。）

モジュールで変数を宣言していますので、
以下、モジュールの先頭に記載しないとコンパイルエラーになります。

Option Explicit

Private rngRows4Hide As Range

Sub HideCustomToggleA()　　'　　実行マクロ
　　On Error GoTo GetRef_
　　If rngRows4Hide.Hidden = False Then　　'　　非表示セルがなければ 非表示
　　　　On Error GoTo 0
　　　　rngRows4Hide.Hidden = True
　　　　Range("合計,差額").EntireColumn.Hidden = True
　　Else　　'　　非表示セルがあれば 再表示
　　　　On Error GoTo 0
　　　　rngRows4Hide.Hidden = False
　　　　Range("合計,差額").EntireColumn.Hidden = False
　　End If
'　　ActiveCell.Activate
　　Exit Sub
GetRef_: '　　非表示行設定
　　Call SetRows4Hide
　　If rngRows4Hide Is Nothing Then MsgBox "非表示にするべき行が見当たりません": Exit Sub
　　Resume
End Sub

Private Sub SetRows4Hide()　　'　　非表示行範囲取得・設定
　　Application.EnableEvents = False
　　Application.ScreenUpdating = False
　　With ActiveSheet.UsedRange
　　　　With .Columns(.Columns.Count + 1)
　　　　　　.Formula = "=1/MOD(ROW(),5)"
　　　　　　On Error Resume Next
　　　　　　Set rngRows4Hide = .Offset(5).SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow
　　　　　　On Error GoTo 0
　　　　　　.ClearContents
　　　　End With
　　End With
　　ActiveSheet.UsedRange ' 手作業での最終セル取得(Ctrl+End等)を元に戻すダミー処理
　　Application.EnableEvents = True
　　Application.ScreenUpdating = True
End Sub

回答No.5

cj_mover

こんにちは、＃３、ｃｊです。
＃３お礼欄へのレスです。

> .Range(sRef).EntireColumn.Hidden = True
> でエラーになってしまいました。

こちらが想定したシートイメージとは違っていたみたいですね。
"合計"、"差額"という名前の付いたセル範囲を基準にできるのでしたら
ずっと簡単にできます。
一応、隣接しないレイアウトに変更されても動く様に
保険を掛けた書き方になりますが、
非表示に設定するサブプロシージャは正味２行ということになります。
よかったら、＃３のSub HideCustomを以下のものに替えて使ってやってください。

Sub HideCustom(Sh As Worksheet) '　 非表示
　　With Sh
' ' 行非表示
　　　　.Range( _
　　　　　　　　"6:9,11:14,16:19,21:24,26:29,31:34,36:39,41:44,46:49,51:54,56:59,61:64,66:69,71:74,76:79,81:84,86:89,91:94,96:99,101:104,106:109,111:114,116:119,121:124,126:129,131:134,136:139,141:144,146:149,151:154" _
　　　　　　　　).EntireRow.Hidden = True
' ' 列非表示
　　　　Application.Union(.Range("合計"), .Range("差額")).EntireColumn.Hidden = True
　　End With
End Sub

回答No.4

米沢 栄蔵（@YON56）

表示/非表示を管理する人が表示/非表示指示をするというポリシーで提案します。

(1)表示/非表示が必要なシートに、その指示をする欄を設けます。
　単純にいきます。1行目、A列を挿入します。
(2)非表示にしたい行/列の対応するセルに「H」という文字を入力する事にします。
　3-6行を非表示にする場合はセルA3-A6に、B-C列の場合はB1-C1に「H」というふうに。
(3)非表示を設定する構文において、
　 行の非表示---A列に入力されている「H」の文字数をカウントし、その個数分回「H」を検出し非表示する処理をします。
　 列の非表示---1行目に入力されている「H」の文字数をカウントし、その個数分回「H」を検出し非表示する処理をします。

非表示→表示は「H」に変わる文字を指定し、その処理をします。

回答No.3

cj_mover

こんにちは。お邪魔します。

非表示も再表示も、行と列それぞれ、一回の処理ですませるように
また、参照文字列で指定するRangeプロパティの高い能力を活用するように
書いてみました。
速さをいうなら、もっと方法はありますが、
比較的扱い易い、遅くはない、方法を選びました。

HideCustomToggle()を実行すると交互に、非表示⇔再表示、するようにしています。

一番下のPrivate Sub PrintRowsRef4Hide()は、
　行が増えたり変更が必要な時に
　非表示にする行の参照文字列をイミディエイトウィンドウに出力するもので、
　VBAの記述上でのセル範囲指定をサポートする為のものです。
　不要なら削除して構いません。

マクロの呼び出しをシート上に配置したActiveXコマンドボタンから行う場合は、
　コマンドボタンを"非表示にする範囲"に掛かって配置しないこと、
　コマンドボタンのプロパティ TakeFocusOnClick をFalseに指定すること、
　の２点、留意してください。
　できれば、ウィンドウ枠の固定機能を使って
　コマンドボタンをスクロールできない位置に配置した方がベターです。

期待の動作と違う場合や、疑問がある場合、補足欄にでも書いてみてください。

Sub HideCustomToggle() '　　メイン　実行マクロ　（Re8140423）
　　With ActiveSheet.UsedRange
　　　　If .Count = .SpecialCells(xlCellTypeVisible).Count Then
' ' 非表示セルがなければ、非表示
　　　　　　Call HideCustom(ActiveSheet)
　　　　Else
' ' 非表示セルがあればすべて再表示
　　　　　　Call UnDoHide(ActiveSheet)
　　　　End If
　　End With
End Sub

Sub UnDoHide(Sh As Worksheet) '　　 再表示
　　With Sh
　　　　.Rows.Hidden = False
　　　　.Columns.Hidden = False
　　End With
End Sub

Sub HideCustom(Sh As Worksheet) '　 非表示
Const SREF_HIDECOL = "・合計・差額・" ' "・"区切り、両端も"・"、追加指定可能
　　Dim r As Range
　　Dim sRef As String
　　With Sh
' ' 行非表示
　　　　.Range( _
　　　　　　　　"6:9,11:14,16:19,21:24,26:29,31:34,36:39,41:44,46:49,51:54,56:59,61:64,66:69,71:74,76:79,81:84,86:89,91:94,96:99,101:104,106:109,111:114,116:119,121:124,126:129,131:134,136:139,141:144,146:149,151:154" _
　　　　　　　　).EntireRow.Hidden = True
' ' 項目名をチェックして一致すれば列非表示ようのセル参照に加える
　　　　For Each r In .UsedRange.Rows(1).Cells
　　　　　　If InStr(SREF_HIDECOL, "・" & r.Value & "・") Then sRef = sRef & "," & r.Address(0, 0)
　　　　Next
　　　　sRef = Mid$(sRef, 2)
' ' 列非表示
　　　　.Range(sRef).EntireColumn.Hidden = True
　　End With
End Sub

Private Sub PrintRowsRef4Hide() '　　（コーディング補助）
' ' 行アドレスをイミディエイトウィンドウに出力
　　Dim sRef As String
　　With ActiveSheet.UsedRange
　　　　With .Columns(.Columns.Count + 1)
　　　　　　.Formula = "=1/MOD(ROW(),5)"
　　　　　　sRef = .Offset(5).SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow.Address(0, 0)
　　　　　　.ClearContents
　　　　　　Debug.Print Len(sRef) & "文字…" & IIf(Len(sRef) < 256, "そのまま使えます", "255を超えているので分割しないと使えません")
　　　　　　Debug.Print """"; sRef; """"
　　　　End With
　　End With
　　sRef = ActiveSheet.UsedRange.Row ' 手作業での最終セル取得(Ctrl+End等)を可能にするダミー処理
End Sub

お礼

返信遅くなりましてすみません。

すごいです。かなり早く処理が終わりました！

ただ、
' ' 列非表示
.Range(sRef).EntireColumn.Hidden = True
でエラーになってしまいました。
デバッグのやり方がまだまだ分からなくて…

ボタンの配置も、上部を固定にして作ることで、
すっきり綺麗に使えるようになりました！

ありがとうございました。

回答No.2

tom04

こんにちは！
非表示する列が決まっていないというのがちょっと厄介ですね！
一例です。

1行目が項目行で、非表示にする列の最初の列の項目名と最後の列の項目名を指定して
列を非表示にする方法にしてみました。

尚、非表示にした後は再表示する必要があると思いますので、
余計なお世話かもしれませんが再表示のコードも一緒に載せておきます。

Sub 行列非表示()
Dim i As Long, staCol As Long, endCol As Long, strSta As String, strEnd As String
Dim c As Range, r As Range
strSta = InputBox("非表示にする列の最初の項目名を入力")
Set c = Rows(1).Find(what:=strSta, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
staCol = c.Column
Else
MsgBox "該当項目名がありません。"
Exit Sub
End If
strEnd = InputBox("非表示にする列の最後の項目名を入力")
Set r = Rows(1).Find(what:=strEnd, LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then
endCol = r.Column
Else
MsgBox "該当項目名がありません。"
Exit Sub
End If
For i = 6 To 151 Step 5
Rows(i).Resize(4).Hidden = True
Next i
Range(Columns(staCol), Columns(endCol)).Hidden = True
End Sub

Sub 再表示()
With ActiveSheet
.Rows.Hidden = False
.Columns.Hidden = False
End With
End Sub

※　入力した項目名がない場合はマクロを実行しないようにしています。
※　項目名が1行目にある！という前提のコードですので、
もし項目名が5行目の場合は
＞Set c = Rows(1).Find・・・
を
＞Set c = Rows(5).Find・・・
のように変更してみてください。
（Set r = Rows(1).Find・・・も同様です）
じっくり考えればもっと良い方法があるかもしれませんが、
今回はとりあえずこの程度で・・・m（_ _）m

お礼

返信遅くなりましてすみません。

使う人が全くエクセルができず、
「ボタンで簡単にぱぱっとやれるの」というリクエストでした。
そのこともちゃんと書いておくべきでした。すみません。

再表示や入力項目名がない時の対策など、
気配りのできたマクロ作り、見習いたいと思います！
ありがとうございました。

回答No.1

misatoanna

次のようなことでしょうか。

Sub Test()
　Dim r As Integer, rng As Range
　For r = 6 To 151 Step 5
　　Select Case r
　　　Case Is = 6
　　　　Set rng = Range(Rows(r), Rows(r + 3))
　　　Case Else
　　　　Set rng = Union(rng, Range(Rows(r), Rows(r + 3)))
　　End Select
　Next
　rng.EntireRow.Hidden = True
　Range("合計", "差額").EntireColumn.Hidden = True
End Sub

5行目以下、5の倍数行以外非表示。
「合計」「差額」と名前付けされた隣り合った列を非表示。

お礼

返信遅くなりましてすみません。

シンプルでいいですね！
倍数で考えるやり方は、今後も役立ちそうです。
名前を付けた列もちゃんと非表示にできました。

ありがとうございました。