Dynamic Range Selection

I am trying to write a Macro that will return a list of the unique values in the column above it. The number of rows will change every time, though the active cell will always be (2, 0) away from the last value in the column. I need to replace 'ActiveCell.Offset(-118, 0)' with something that will select Row 1 every time for whatever column the active cell is in. Is that 'Range(A1)? Then I need to replace 'Range("A1:A117")' with the whole whole range from the first row to two above the Active cell. It will also be the last row to have any content. I tried 'xlUp' and 'xlDown" but there may be blank cells in between and I don't know how many. I expect that it would throw it off.

Here is what I have.


ActiveCell.Offset(-118, 0).Range("A1:A117").AdvancedFilter Action:= _
xlFilterCopy, CopyToRange:=ActiveCell, Unique:=True

Thank you for the help!

That is perfect. Thank you so much.