Loop all worksheets without three specific sheets

user_18331849 0
Viewed 51

This code doesn't loop from one 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.

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
autofilter vba excel
2 Answers

(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

user_75997980

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
user_98140690
Related