Macro Adjustment

tommychowdah

New Member
Joined
Dec 26, 2017
Messages
31
Hi!

I would like to adjust this macro so that instead of being applied to every sheet, it looks up a range of tab names, and is only applied to those tabs. Any help would be much appreciated!

Sub Hide_All_Rows()
Dim r As Range
Dim ws As Worksheet
On Error GoTo err_handler
Application.ScreenUpdating = False
For Each ws In Worksheets
For Each r In ws.Range("ZZ1:ZZ1000")
If r.Value = 1 Then
r.EntireRow.Hidden = True
End If
Next r
Next ws

Application.ScreenUpdating = True
Exit Sub
err_handler:
MsgBox "Sheet: " & ws.Name & " caused an error"
End Sub
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
How about
Code:
Sub Hide_All_Rows()
   Dim r As Range
   Dim Sht As Variant
   
   On Error GoTo err_handler
   Application.ScreenUpdating = False
   For Each Sht In [COLOR=#ff0000]Sheets("Summary").Range("C1", Sheets("Summary").Range("C" & Rows.Count).End(xlUp))[/COLOR]
      For Each r In Sheets(CStr(Sht)).Range("ZZ1:ZZ1000")
         If r.Value = 1 Then r.EntireRow.Hidden = True
      Next r
   Next Sht
   
   Application.ScreenUpdating = True
   Exit Sub
err_handler:
   MsgBox "Sheet: " & Sheets(Sht).Name & " caused an error"
End Sub
Change the part in red to match the sheet & range where you have the sheet names
 
Upvote 0
Hi,

Thank you for the response, there are over 100 tabs, so typing each one out is not practical. Can you create a named range and have the macro referenec the named range?

Best,
Chowdah
 
Upvote 0
There's no real difference between what I've supplied & a named range, you still need to list the sheets somewhere.
Do you already have a list of the sheet names you want to include? & if so where are they?
 
Upvote 0
In that case change the line in red (in post#2) to this
Code:
Sheets("List").Range("E3", Sheets("List").Range("E" & Rows.Count).End(xlUp))
 
Upvote 0
Remove the error handler & see what line causes the problem & what the error message is.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top