[SOLVED] Excel: Using Advanced Distinct Filter on already filtered Range [1004: Database or table range is not valid.]

Excel: Using Advanced Distinct Filter on already filtered Range [1004: Database or table range is not valid.]

I try to filter my list to all entries with "1" and the items that match that criteria I want to be listed uniquely to iterate through that list later.

But I get the error message: 1004 "Database or table range is not valid."

This is the code I am trying to use:

Sub Schritt_42temp()

With Sheets("Tabelle1")
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

    ActiveSheet.Range("$A$1:$BZ$" & lastRow).AutoFilter Field:=60, Criteria1:=1

    Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible).Select
    Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("BG2:BG6000"), Unique:=True

End Sub

It works if I don't filter the list, but it's important that he only selects the filtered items:

Sub Schritt_42temp()

With Sheets("Tabelle1")
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With

   'Disable Filtering
   'ActiveSheet.Range("$A$1:$BZ$" & lastRow).AutoFilter Field:=60, Criteria1:=1

    Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible).Select
    Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("BG2:BG6000"), Unique:=True

End Sub

Please note that I am a total beginner to VBA programming. Do you have any advice for me?

Solution

since your goal is to have a list to unique values to iterate through , you can use Dictionary object to get it

Sub Schritt_42temp()
    Dim cel As Range
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

    With Sheets("Tabelle1")

        With .Range("BZ1", Cells(.Rows.Count, 1).End(xlUp))
            .AutoFilter Field:=60, Criteria1:=1
            For Each cel In .Columns(1).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
                dict(cel.Value) = dict(cel.Value) & vbNullString
            Next
        End With
        .AutoFilterMode = False

        Dim valuesToIterate As Variant
        valuesToIterate = dict.keys '<-- here you get an array filled with column A unique values corresponding to column BH 1's

    End With
End Sub