How to increment loop with advanced filter in VBA excel

Question

Now I'm using a script which detects all cells that are not empty and copies those cells to another sheet. The script is made to do this for one column at the time. Now I have a sheet with over 200 columns and I would like to loop this script.

As you can see I tried to loop the columns, but I receive an error when I run the code.

Code I'm using without a loop:

Sub uniek()

Sheets("Rekenblad").Range("A1:A93500").AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets("Uniek").Range("A1"), Unique:=True

Sheets("Rekenblad").Range("B1:B93500").AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets("Uniek").Range("B1"), Unique:=True

Sheets("Rekenblad").Range("C1:C93500").AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets("Uniek").Range("C1"), Unique:=True

End Sub

What I tried:

Sub uniek2()

For Col = 1 To 100
Sheets("Rekenblad").Range(Cells(1, Col), Cells(1, Col)).AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets("Uniek").Range(Cells(1, Col)), Unique:=True
Next Col

End Sub

Answer 1

The Cells range object works different than the Range object.

You can use Cells(Row, ColumnNumber or Cells(Row, "ColumnLetter")

Next to that, your range objects aren't all set to refer to a specific wb and ws. If you don't specify these, VBA will instead refer to the active wb/ws.

So, a correct loop would look something like:

Sub uniek2()
Dim ColNr As Integer
Dim MaxRow As Long
Dim sht As Sheet

Set sht = Workbooks(REF).Sheets("Rekenblad")

MaxRow = 93500

With sht

    For ColNr = 1 To 100
        .Range(.Cells(1, ColNr), .Cells(MaxRow, ColNr)).AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets("Uniek").Range(Workbooks(REF).Sheets("Uniek").Cells(1, ColNr)), Unique:=True
    Next ColNr

End With

End Sub

Make sure you edit REF to the correct WB reference.