Excel Macro to hide the same rows across multiple specfic sheets

esitze

New Member
Joined
Aug 24, 2018
Messages
18
Good Morning,
I borrowed this code and tried to modify to do what I need with no luck. I have 7 sheets in a workbook named MON-SUN. Mon is the sheet with blanks for data entry. Tues-Sun have formulas to transfer the info through the selected sheets. I would like to have a macro on sheet Mon that hides all the blank rows and I would like that to hide the same rows on Tues-Sun. This is what I have so far

Dim wsMySheet As Worksheet
Dim lngMyRow As Long

Application.ScreenUpdating = False

For Each wsMySheet In ThisWorkbook.Sheets
Select Case wsMySheet.Name
Case Is = "MON", "TUES", "WED", "THUR", "FRI", "SAT", "SUN"
For lngMyRow = 73 To 10 Step -1 'Need to work backwards through the rows when hiding or deleting
If Len(wsMySheet.Range("A" & lngMyRow)) = 0 Then
wsMySheet.Range("A" & lngMyRow).EntireRow.Select
Sheets(Array("MON", "TUES", "WED", "THUR", "FRI", "SAT", "SUN")).Select
wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = True
Else
wsMySheet.Range("A" & lngMyRow).EntireRow.Hidden = False
End If
Next lngMyRow
End Select
Next wsMySheet

Application.ScreenUpdating = True

End Sub


It works great on Mon but then stops. Line 9 is the error line.
Any help will be appreciated!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
How about
VBA Code:
Sub esitze()
   Dim Cl As Range, Rng As Range
   Dim Sht As Worksheet
   
   For Each Cl In Sheets("MON").Range("A10:A73")
      If Cl = "" Then
         If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
   Next Cl
   Rng.EntireRow.Hidden = True
   For Each Sht In Sheets(Array("TUES", "WED", "THUR", "FRI", "SAT", "SUN"))
      Sht.Range(Rng.Address(, , , 0)).EntireRow.Hidden = True
   Next Sht
End Sub
 
Upvote 0
Thanks for the quick reply, I am getting a Next without For Compile error on the "Next C1" line
 

Attachments

  • MrExcel1.png
    MrExcel1.png
    142.9 KB · Views: 2
Upvote 0
Oops, missed an End If line
VBA Code:
Sub esitze()
   Dim Cl As Range, Rng As Range
   Dim Sht As Worksheet
   
   For Each Cl In Sheets("MON").Range("A10:A73")
      If Cl = "" Then
         If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
      End If
   Next Cl
   Rng.EntireRow.Hidden = True
   For Each Sht In Sheets(Array("TUES", "WED", "THUR", "FRI", "SAT", "SUN"))
      Sht.Range(Rng.Address(, , , 0)).EntireRow.Hidden = True
   Next Sht
End Sub
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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