CODEWITHSUNDEEP
vbaexcel
axelmartinez
axelmartinez

Reputation: 3

subscript out of range VBA Excel, advanced filter macro

I'm stuck on the last bit of this Excel macro. I adapted it from this template to do advanced filtering on a list of data based on criteria the user enters.

I get a subscript out of range error on line Sheets("Data (2)").ListObjects("table4").TableStyle = "TableStyleMedium2"

table4 where all my data is hanging out; I named this by selecting the relevant cells and then naming that range.

added the Dim table4 As String line above; it was not in the original code but when I looked up the error in Excel help it said I need to declare the array first. Data are just text strings so string is fine - I won't be calculating anything after the filter is complete.

Any ideas?

Option Explicit

Private Sub btnFilter_Click()
    Application.ScreenUpdating = False

    ' clear old data first
    Dim n As Long
    n = Cells(Rows.Count, "A").End(xlUp).Row
    If n > 23 Then
        Rows("24:" & CStr(n)).Delete Shift:=xlUp
    End If

    With Sheets("Data (2)")
        .Select

        ' apply filter
        .Range("A:AW").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("Criteria2"), Unique:=False

        ' select filtered rows
        Dim rngFilter As Range
        Set rngFilter = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 9)

        ' count number of filtered rows
        On Error Resume Next
        n = 0
        n = rngFilter.SpecialCells(xlCellTypeVisible).Rows.Count
        On Error GoTo 0

        If n = 0 Then
            Sheets("Filter (2)").Select

            ' skip copying
            GoTo skip_copying
        End If

        ' copy selection
        rngFilter.Select
        Selection.Copy
    End With

    ' paste new data
    Sheets("Filter (2)").Select
    Sheets("Filter (2)").Range("A24").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("Filter (2)").Range("A24").Select

skip_copying:
    ' remove filter
    Sheets("Data (2)").ShowAllData

    ' table style
    Dim table4 As String
    Sheets("Data (2)").ListObjects("table4").TableStyle = "TableStyleMedium2"

    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 1665

Answers (1)

Mike
Mike

Reputation: 620

You created a named range, not a ListObject, so your reference to table4 is unrecognized. Here are instructions for making your data range into a ListObject: https://msdn.microsoft.com/en-us/library/eyfs6478.aspx

Upvotes: 0

Related Questions