CODEWITHSUNDEEP
excelvba
Wosh
Wosh

Reputation: 1625

Can't specify the exact range in Excel vba macro

Today is my first day fighting the VBA macros in Excel. All I want to do is to specify a column, get the distinct values from it and copy them in another column, found on a different sheet. I was reading a lot and this is what I came up with : 

Sub TestS()
    Dim lastRow As Long
    lastRow = Worksheets(3).Range("A" & Rows.count).End(xlUp).Row
    Worksheets(3).Range(Cells(3, 1), Cells(lastRow, 1)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Worksheets(2).Range("A3"), Unique:=True
End Sub

lastRow currently points to the correct position, but I get the error :

Run-time error 1004 The extract range has a missing or invalid field name.

Obviously there is something wrong with the syntax but I tried many different ways and none of them is working.

Upvotes: 2

Views: 1177

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Just qualify Cells():

Sub TestS()
    Dim lastRow As Long
    With Worksheets(3)
        lastRow = .Range("A" & Rows.Count).End(xlUp).Row
        .Range(.Cells(1, 1), .Cells(lastRow, 1)).AdvancedFilter _
            Action:=xlFilterCopy, _
            CopyToRange:=Worksheets(2).Range("A3"), Unique:=True
    End With
End Sub

Upvotes: 2

AnalystCave.com
AnalystCave.com

Reputation: 4974

Tasks like these I suggest using simple SQL queries. This way you establish a connection to the source worksheet that can be refreshed at any time (like a PivotTable) and the code is more simple and flexible. The query:

SELECT DISTINCT * FROM [Sheet1$]

How to use SQL in Excel? Look for "Microsoft Query" in the "Data" ribbon, section External data. Or simply download my Add-In for Excel here: http://blog.tkacprow.pl/?page_id=130

Upvotes: 0

Related Questions