Macro / VBA: Macro Failling

[tommychowdah]

Hi!

I have a macro that loops throughout a list of tab names. The macro is only applied to certain tabs. The macro fails on the "Sheets(w).Select" line. Any suggestions?

Sub Hide_Unused_Rows()


Dim ws As Worksheet
Dim Worksheets As Variant


Application.ScreenUpdating = False


Worksheets = Range("TAB_ROWS").Value


For Each w In Worksheets
Sheets(w).Select
For H = 1 To 10000
If Cells(H, 27).Value = "1" Then
Cells(H, 27).EntireRow.Hidden = False
Else
Cells(H, 27).EntireRow.Hidden = True
End If
Next H
Next w

Application.ScreenUpdating = True


End Sub

 

[Joe4]

You declared "ws"

[COLOR=#333333]Dim ws As Worksheet[/COLOR]

but then used "w"

[COLOR=#333333]For Each w In Worksheets[/COLOR]
[COLOR=#333333]Sheets(w).Select[/COLOR]

Try this:

[COLOR=#333333]For Each ws In Worksheets[/COLOR]
[COLOR=#333333]ws.Select[/COLOR]

Since ws is already a worksheet object, you don't need to use "Sheets" with it.

 

[tommychowdah]

Now receiving an Object error. here is the new code.

Sub Hide_Unused_Rows()


Dim ws As Worksheet
Dim Worksheets As Variant


Application.ScreenUpdating = False


Worksheets = Range("TAB_ROWS").Value


For Each ws In Worksheets
ws.Select
For H = 1 To 10000
If Cells(H, 27).Value = "1" Then
Cells(H, 27).EntireRow.Hidden = False
Else
Cells(H, 27).EntireRow.Hidden = True
End If
Next H
Next ws


Application.ScreenUpdating = True


End Sub

 

[Joe4]

OK, let's avoid use reserved words like "Worksheets". That can cause confusion and unexpected errors.
Is "TAB_ROWS" a named range that list all the worksheets you want to loop through?

If so, try this:

Sub Hide_Unused_Rows()

    Dim myWS As Range
    Dim ws As Range
    Dim H As Long

    Application.ScreenUpdating = False
    
    Set myWS = Range("TAB_ROWS")

    For Each ws In myWS
        Sheets(ws.Value).Select
        For H = 1 To 10000
            If Cells(H, 27).Value = "1" Then
                Cells(H, 27).EntireRow.Hidden = False
            Else
                Cells(H, 27).EntireRow.Hidden = True
            End If
        Next H
    Next ws

    Application.ScreenUpdating = True

End Sub

 

[tommychowdah]

Thank You!

The Macro crashes on "Sheets(ws.Value).Select" and says Subscript our of range. The help is much appreciated!

 

[Joe4]

It might mean that you have a value in your list that does not match any sheet name (or the sheet is hidden).
Try this variation with error coding which should tell you which value it does not like:

Sub Hide_Unused_Rows()

    Dim myWS As Range
    Dim ws As Range
    Dim H As Long

    Application.ScreenUpdating = False
    
    Set myWS = Range("TAB_ROWS")

    For Each ws In myWS
        On Error GoTo err_chk
        Sheets(ws.Value).Select
        On Error GoTo 0
        For H = 1 To 10000
            If Cells(H, 27).Value = "1" Then
                Cells(H, 27).EntireRow.Hidden = False
            Else
                Cells(H, 27).EntireRow.Hidden = True
            End If
        Next H
    Next ws

    Application.ScreenUpdating = True

    Exit Sub
    
err_chk:
    MsgBox "Problem wirh sheet name: " & ws.Value
    Application.ScreenUpdating = True

End Sub