Run Same VBA Macro Code On Multiple Sheets

[NYGiantsGuy1]

Hi,

Im a total novice and did not know where else to turn so here goes. I found this code to hide blank rows and it works but I need it to hide rows on select worksheets in my file.

The worksheets it needs to run on are"SHEET5", "SHEET6", "SHEET7", "SHEET8", "SHEET9", "SHEET10", "SHEET11", "SHEET12" , and "SHEET13".


Sub hideEmptyRows2()


Application.ScreenUpdating = False


For i = 3 To 120
If ActiveSheet.Cells(i, 1) = "" Then
ActiveSheet.Cells(i, 1).EntireRow.Hidden = True
End If
Next i


Application.ScreenUpdating = True


End Sub

Thanks in advance!

Best,
Aidan

[snb]

Sub M_snb()
  for j=6 to 13
    sheets("sheet" & j).columns(1).specialcells(4).entirerow.hidden=-1
  next
End Sub

[Paul_Hossler]

Another way

Personally, if there were a indicator on the worksheet (e.g. 'If ws.Range("A1") = "SOMETHING"') that you could trigger off of, it would be more flexible since you wouldn't need to keep editing the list. What happens if you add Sheet14 or rename Sheet9?

Option Explicit
Sub hideEmptyRows2()
    Dim v As Variant
    
    Application.ScreenUpdating = False
    For Each v In Array("SHEET5", "SHEET6", "SHEET7", "SHEET8", "SHEET9", "SHEET10", "SHEET11", "SHEET12", "SHEET13")
        On Error Resume Next    '   in case there are no blank cells
        Worksheets(v).Range("A3:A120").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
        On Error GoTo 0
    Next
    Application.ScreenUpdating = True
End Sub



Sub hideEmptyRows2a()
    Dim ws As Worksheet
    
    
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("A1") = "SOMETHING" Then
            On Error Resume Next    '   in case there are no blank cells
            ws.Range("A3:A120").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
            On Error GoTo 0
        End If
    Next
    Application.ScreenUpdating = True
End Sub

[NYGiantsGuy1]

Hi snb,

When I try to execute the code I get a "runtime error 9 "subscript out of range". Does it make a difference if my sheets have names i.e. Sheet 5 = Financial Proof?

Thanks for your help!

-Aidan

[NYGiantsGuy1]

Hi Paul,


Im a little confused about what you mean by If ws.Range("A1") = "SOMETHING" Then . So Column A will have employee names that will always be different. Is there something generic I can enter for "soemthing"? Like "IsNotBlank"?


I tried entering a name into this just for testing purposes, If ws.Range("A1") = "Jeff" Then, but when I run the code you posted nothing happens.


Thanks for your help!


-Aidan

[snb]

The name of the sheets must be, like you indicated yourself, "sheet6", "sheet7", etc.
Post a file to show what your workbook looks like.

Sub M_snb() 
  on error resume next

  For each sh in sheets 
    sh.columns(1).specialcells(4).entirerow.hidden=-1 
  Next 
End Sub

[Paul_Hossler]

Hi Paul,

Im a little confused about what you mean by If ws.Range("A1") = "SOMETHING" Then . So Column A will have employee names that will always be different. Is there something generic I can enter for "something"? Like "IsNotBlank"?
I tried entering a name into this just for testing purposes, If ws.Range("A1") = "Jeff" Then, but when I run the code you posted nothing happens.
Thanks for your help!
-Aidan

Since your original test started in row 3, I was suggesting that if there were some kind of marker or content that could be used as a test

For example, many times a sheet would have something like "Date: 3/1/2016 Report of stuff" in A1

Instead of hard coding sheet names ("SHEET5") which will break the macro when you rename it to "Financial Proof", it's better to make the macro a little smarter


So something like

For Each ws In ActiveWorkbook.Worksheets 
        If Left(ws.Range("A1").Value,5) = "Title" Then

or

For Each ws In ActiveWorkbook.Worksheets 
        If Len(ws.Range("A1").Value) > 0 Then

would only process the sheets that pass the text

THAT's the reason I hate to hard code names like that into a macro

The worksheets it needs to run on are"SHEET5", "SHEET6", "SHEET7", "SHEET8", "SHEET9", "SHEET10", "SHEET11", "SHEET12" , and "SHEET13".

Are you saying that the names of the worksheets as shown at the bottom of the window are really not "SHEET5", etc.?

[NYGiantsGuy1]

Hey sbn,

Here is a file.

Test File.xlsm

Hey Paul,

That is correct the sheets at the bottom of the window all have unique names. I uploaded a file for reference.

[Paul_Hossler]

Try this

Option Explicit

Sub hideEmptyRows2b()
    Dim ws As Worksheet
     
    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("A1") = "Census Data" Then
            On Error Resume Next '   in case there are no blank cells
            ws.Range("A3:A120").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
            On Error GoTo 0
        End If
    Next

    Application.ScreenUpdating = True

End Sub

[NYGiantsGuy1]

Thanks Paul! This works perfectly!