macro to loop all Worksheet without 3 specific Sheets -doesn't work-CodePudding

i've question, this Macro doesn't lop from Worksheet to another , the Idea is to hide all rows which don't contain "X" in column A for all worksheets without three specified worksheets, i will be grateful if somebody could help me.

Option Explicit
Sub test()

Dim ws As Worksheet
Dim C As Range

For Each ws In ThisWorkbook.Worksheets

 If ws.Name <> "Übersicht" Or ws.Name <> "Definitionen" Or ws.Name <> "Abkürzungen" Then
   
    For Each C In Range("A6:A2000").Cells
        If C.Value = "" Then
            C.EntireRow.Hidden = True
        Else
            C.EntireRow.Hidden = False
        End If
    Next C
    
  Else
 End If
   
Next ws
 
End Sub

CodePudding user response：

(a) You need to specify the worksheet:

For Each C In ws.Range("A6:A2000")

(b) Your If-condition when you check the sheetname is wrong, you need to change the Or to And, else it will always be True and you will work on an all sheets.

(c) Your code will probably be very slow. Set all rows to visible, collect the rows that you want to hide in a Range-variable (use Union) and set all of them to hidden in one go.

You also whould optimize your code by checking the rows in use per worksheet and work only on that rows

CodePudding user response：

Hide Blank Rows Using AutoFilter

Option Explicit

Sub HideBlankRows()
    
    Const wsNamesNoList As String = "Übersicht,Definitionen,Abkürzungen"

    Dim wsNamesNo() As String: wsNamesNo = Split(wsNamesNoList, ",")

    Dim ws As Worksheet
    Dim drg As Range ' Data Range (no headers)
    Dim vrg As Range ' Visible (Data) Range
    
    For Each ws In ThisWorkbook.Worksheets
        If IsError(Application.Match(ws.Name, wsNamesNo, 0)) Then
            If ws.AutoFilterMode Then ws.AutoFilterMode = False
            With ws.Range("A5:A2000") ' 5 is the header row
                .EntireRow.Hidden = False
                Set drg = .Resize(.Rows.Count - 1).Offset(1)
                .AutoFilter 1, "="
                On Error Resume Next
                    Set vrg = drg.SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
            End With
            ws.AutoFilterMode = False
            If Not vrg Is Nothing Then
                vrg.EntireRow.Hidden = True
                Set vrg = Nothing
            End If
        End If
    Next ws
    
    MsgBox "Blank rows hidden.", vbInformation
    
End Sub