none
Applying autofilter throughout workbook RRS feed

  • Question

  • im looking for the vba coding that will work though out my  workbook but misses out 3 worksheets every time I try it comes up with debug errors
    Friday, October 20, 2017 9:40 AM

All replies

  • Please post a copy of the code you are using.

    Regards, OssieMac

    Friday, October 20, 2017 10:38 AM
  • Hi Jhcousins1091,

    You could try to check worksheet's name when working though workbook.

    Here is an example that uses a dictionary for checking name.

    Sub Test()
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "Sheet1", 1
    dict.Add "Sheet2", 1
    dict.Add "Sheet3", 1
    For Each ws In ActiveWorkbook.Worksheets
    If Not dict.Exists(ws.Name) Then
    'it will skip the worksheet Sheet1,Sheet2,Sheet3
    MsgBox ws.Name
    End If
    Next ws
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 23, 2017 6:09 AM
  • Hi Jhcousins,

    Could you share us the error message? It would be helpful if you could share us your workbook.

    In my option, we could not apply autofilter on empty worksheet, I suggest you make a test with below code.

    Sub test()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If IsBlank(ws) <> True Then
            ws.UsedRange.AutoFilter
        End If
    Next ws
    End Sub
    
    Function IsBlank(sh As Worksheet)
        Set LastCell = sh.Cells.SpecialCells(xlCellTypeLastCell)
        If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then IsBlank = True
    End Function
    

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 25, 2017 7:24 AM