Excel Macro to hide the same rows across multiple specfic sheets

esitze

New Member
Joined
Aug 24, 2018
Messages
7
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!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
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
 

esitze

New Member
Joined
Aug 24, 2018
Messages
7
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: 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
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
 

esitze

New Member
Joined
Aug 24, 2018
Messages
7
That worked like a CHARMMMMMM! Thank you very much for the help!!!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,769
Members
416,202
Latest member
donya ba

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
Top