I'd use something like:

Dim myRng as range
dim toCell as range

with worksheets("working"
'start in row 2 of column A and start at the bottom of the column
'and come up to the last used cell in column A.
'If the only header is in row 1, use A1 and not A2
set myrng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

set tocell = worksheets("current").range("a5")

myrng.advancedfilter Action:=xlFilterCopy, _
CopyToRange:=tocell, Unique:=True, criteriarange:=""

I never would have specificed the criteriarange until today--I read a blog post
by Debra Dalgleish:

http://blog.contextures.com/archives...lter-in-excel/




Michael Conroy wrote:

I have data being pasted into a worksheet from which I need to do a unique
sort on the first column (EmployeeID) and copy the results to another
worksheet. My problem is with referencing the range address. Since the number
of rows can vary, what is the proper way to get that full range into the
source line. So far I have this:

Dim SourceArea, TargetArea as Variant

Set TargetArea = Application.Sheets("Current").Range("A5")
Application.Sheets("Working").Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select '<-Gets all rows
Set SourceArea = ActiveCell.Address

Range([SourceArea]).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range([TargetArea]), Unique:=True

If Excel has a better way to reference a range, please let me know. My first
instinct is to assign it to a variable. As always thanks for the help.
--
Michael Conroy
Stamford, CT

--

Dave Peterson