Range to Variable for Sort

[Michael Conroy]

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.

 

[Matthew Herbert]

Michael,

The Set Statement is used to create objects. As it relates to ranges, you
can do something like the following:

Dim Rng As Range
Set Rng = Range("A1:B5")

This creates a range object assigned to "A1:B5". This range object takes on
all the attributes of a range (see the Intellisence when it appears after
typing the dot operator or see the Object Browser, Range Class, for more
information). If you don't qualify your range (i.e. specify a workbook
and/or worksheet), it will take the ActiveWorkbook and ActiveSheet
qualifiers; this is an important concept. Also, if you don't dimension your
variable with a data type, it is automatically typed as Variant.

You can change out your code to something like the following:

Dim rngSource As Range
Dim rngTargetArea As Range

Set rngTargetArea = Sheets("Current").Range("A5")

Set rngSource = Sheets("Working").Range("A2")
Set rngSource = Range(rngSource, rngSource.End(xlDown))

rngSource.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=rngTargetArea, _
Unique:=True

If one of the filter parameters needs a string argument instead of a range
argument, then you can simply do rngTargetArea.Address instead of
rngTargetArea.

Best,

Matthew Herbert

 

[Dave Peterson]

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/2009/11/11/a-clogged-filter-in-excel/

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.