Assign range to variable for sort

[Michael Conroy]

I am trying to do a unique sort to another location and I recorded this code.

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:A69").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"AA1"), Unique:=True

The next time there might be more than sixty-seven rows of data, so I did
this, which does not work.

Dim TargetArea as Range
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Set TargetArea = Selection.ActiveCell
Range([TargetArea]).... the rest of the sort code

How does one grab a range of cells, assign it to a variable, and refer to it
further down in the code? If this is not the proper way to do it, please feel
free to let me know. And thanks in advance for any help on this.

 

[OssieMac]

Hi Michael,

My first question is why start from A2? If you do not include the column
header in an Advanced filter/copy/unique then the first value is used as the
header in the output and you will actually see 2 elements the same. The
header and it will be repeated further down your list.

Having said that, is there any reason you cannot use the column for the
Advance filter range?

Example: (Note use of Columns in lieu of Range.)

Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"AA1"), Unique:=True

If you start getting errors when the code is changed because the header is
incorrect then delete all of the data in the Copy To range and also maybe the
Defined name 'Extract'. (Look up defined names in Help if you don't know how
to do this.)

 

[OssieMac]

Hello again Michael,

Another thing you should do when using Advanced filter with code is to clear
the old extracted data before re-running the code. If you don't do this
before you re-run the code and there are actually less elements in the
extracted data there was previously then you will still have extra data at
the bottom of the list from the previous run.

Because Excel creates a Defined name "Extract" for the position of the
column header of the extracted range, you can use it to identify the range to
be cleared.

Example: (Insert immediately before the advanced filter code line)
Range("Extract", Range("Extract").End(xlDown)).ClearContents

Note: You cannot insert that line in your code until after the advanced
filter has been run at least once.