Excel Macro to hide rows across multiple sheets

1
votes

I have a macro that hides rows based on cell values. I am attempting to use this across multiple sheets in the workbook, but not all of them. My code below seems to run the macro multiple times in the same sheet. 

Sub HideRowsWbk()
Dim LastRow As Long
Dim Rng As Range
Dim ws As Worksheet

Application.ScreenUpdating = False
With ThisWorkbook
    For Each ws In .Worksheets
        Select Case ws.Name
        Case "0000_Index", "000_BidItems", "000_EntrySheet", "000_PayReqs"
             'do nothing - exclude these sheets

        Case Else
            With ws
                LastRow = Range("A65536").End(xlUp).Row '
                Set Rng = Range("M15:M" & LastRow) 'choose column where value exists

                For Each cell In Rng
                    If cell.Value = "0" Or cell.Value = "-" Then  'checks if cell value is 0 or -
                        cell.EntireRow.Hidden = True
                    End If
                Next cell
            End With
        End Select
    Next ws
End With

Application.ScreenUpdating = True

End Sub

Please tell me what I have done wrong and how I can fix this. Also please show me how I can improve my minimal coding skills. I am using Excel 2007.

Thank you.

1
excelexcel-2007vba

1 Answers

1
votes

use:

LastRow = .Range("A65536").End(xlUp).Row '
Set Rng = .Range("M15:M" & LastRow) 'choose column where value exists

the "." makes it work with ws