Hide entire rows based on specific value in cells across rows and columns on multiple sheets

[ExceLoki]

i have a workbook that contains many sheets that pull values from an input sheet. some of the formulas on many sheets say "HIDE ROW" if the input does not work for those sections.
i am trying to come up with vba to hide entire rows if anywhere in the row has the shown value "HIDE ROW". this goes across several sheets, rows, and columns.
any help is apperciated!

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this with a copy of your workbook.

Sub Hide_Rows()
  Dim ws As Worksheet
  Dim rFound As Range
  
  Application.ScreenUpdating = False
  For Each ws In Worksheets
    ws.Cells.EntireRow.Hidden = False
    With ws.UsedRange
      Set rFound = .Find(What:="HIDE ROW", LookIn:=xlValues, MatchCase:=False)
      If Not rFound Is Nothing Then
        Do
          rFound.EntireRow.Hidden = True
          Set rFound = .Find(What:="HIDE ROW", LookIn:=xlValues, MatchCase:=False)
        Loop Until rFound Is Nothing
      End If
    End With
  Next ws
  Application.ScreenUpdating = True
End Sub

 

[ExceLoki]

thanks for the welcome!

i get an error when running this. the bold/underlined line is the highlighted part when debug

Sub HideRows2()

Dim ws As Worksheet
Dim rFound As Range

Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Cells.EntireRow.Hidden = False
With ws.UsedRange
Set rFound = .Find(What:="HIDE ROW", LookIn:=xlValues, MatchCase:=False)
If Not rFound Is Nothing Then
Do
rFound.EntireRow.Hidden = True
Set rFound = .Find(What:="HIDE ROW", LookIn:=xlValues, MatchCase:=False)
Loop Until rFound Is Nothing
End If
End With
Next ws
Application.ScreenUpdating = True
End Sub

 

[ExceLoki]

here are a few examples of the sheets i'm using
-----------

HIDE ROW example.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
25
26
27
28
32
33
34
35
36
37
38
39												N/A										N/A
40																HIDE ROW						HIDE ROW
41																HIDE ROW						HIDE ROW
42	For married participants, the qualified joint and survivor annuity is the 50% Joint and Survivor Annuity.
43
44
45
46
47
48
49
50
51
52
53
54
55
56
1

----------------------

HIDE ROW example.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
25
26
27
28
32
33
34
35
36
37
38
39												N/A									N/A
40	HIDE ROW															HIDE ROW					HIDE ROW
41																HIDE ROW					HIDE ROW
42	HIDE ROW
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
4

-------------

HIDE ROW example.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30												N/A									N/A
31
34					HIDE ROW											HIDE ROW					HIDE ROW
35
36																HIDE ROW					HIDE ROW
37
40												N/A				HIDE ROW					N/A
41
42
43
44
45
46
47
48
49
6

-------------

HIDE ROW example.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18		·		HIDE ROW
19
20
21
22
23
24
25
26
27
28
29
30												N/A									N/A
31
34					HIDE ROW											HIDE ROW					HIDE ROW
35
36																HIDE ROW					HIDE ROW
37
40												N/A				HIDE ROW					N/A
41
42		HIDE ROWS
43
44
45
46
47
48
49
50
51
52
53
7

 

[Peter_SSs]

here are a few examples of the sheets i'm using

Thanks for the samples which clearly show the issue. Merged cells and vba often do not sit well together.

However, you could try this version for your circumstances.

Sub Hide_Rows_v2()
  Dim ws As Worksheet
  Dim rFound As Range
 
  Application.ScreenUpdating = False
  For Each ws In Worksheets
    With ws.UsedRange
      Set rFound = .Find(What:="HIDE ROW", LookIn:=xlValues, MatchCase:=False)
      If Not rFound Is Nothing Then
        Do
          rFound.MergeArea.EntireRow.Hidden = True
          Set rFound = .Find(What:="HIDE ROW", LookIn:=xlValues, MatchCase:=False)
        Loop Until rFound Is Nothing
      End If
    End With
  Next ws
  Application.ScreenUpdating = True
End Sub

 

[ExceLoki]

that worked wonderfully! thank you!

one more question, is there an easy way to unhide those rows and rest everything?

 

[Peter_SSs]

.. and rest everything

I don't know what you mean by that but this should unhide all rows on all worksheets.

Sub Unhide_Rows()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    ws.Cells.EntireRow.Hidden = False
  Next ws
End Sub

 

[ExceLoki]

I don't know what you mean by that but this should unhide all rows on all worksheets.

meant reset, not rest

 

[ExceLoki]

for some reason that did not work

 

[Peter_SSs]

for some reason that did not work

Is there one or more worksheets in the workbook that are protected?