AdvancedFilter and setting the range

[Nash]

These of course are two separate questions:
I'm writing a sub that is supposed to filter a sorted column of
numbers, giving me a list of unique values on another worksheet. Then
I want to count the number of occurrences of each value and put them
in column B on that other worksheet, obtaining a table of frequencies.
First, AdvancedFilter does the job almost right, since I get two
repeated instances of the lowest value. Why would it do that? Where do
I look for an error? I've checked the data and even set the format so
that I would be sure it's all numbers, but it still gives the same
result.
Also, when I try do set the range on the second worksheet using

Set rfilt = Worksheets("fx").Range(Cells(2, 1), Cells(counter, 1))

I get the 1004 run-time error: Application-defined or object-defined
error.
The same syntax works like a charm at the beginning of the sub, where
I define the range holding the data. Why doesn't it work here?

TIA,
Nash

 

[OssieMac]

Hi Nash,

Do you have a column header on the source data range for advanced filter?
Must have one otherwise Excel thinks the first cell is the column header nad
uses it as such in the output. (Is it the first value in the column that is
repeated.)

Try this method. The reason your code works at the beginning is probably
because it is assigning the range to the variable on the active sheet. The
later code, sheet fx is probably not the active sheet. Try the following
method.

With Worksheets("fx")
Set rfilt = .Range(.Cells(2, 1), .Cells(counter, 1))
End With

 

[OssieMac]

Hi again Nash,

Just an after thought.

A little added lesson in Excel. The reason that your code did not work when
the referenced sheet was not the active sheet is because Excel thinks that
Cells(.... belongs to the active sheet and the range reference is another
sheet.

The following code should work just as well as the previous code that I
posted. Each Cells function is preceded with the worksheet identifier.

(Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.)

Set rfilt = Worksheets("fx").Range(Worksheets("fx").Cells(2, 1), _
Worksheets("fx").Cells(counter, 1))

In the following code using With / End With, note the dot in front of Range
and Cells. This ties them to Worksheets("fx") and is just a shorthand way of
writing it so you do not have to prefix all the functions with the worksheet
name:-

With Worksheets("fx")
Set rfilt = .Range(.Cells(2, 1), .Cells(counter, 1))
End With

Regards,

OssieMac

 

[Nash]

Thanks, Ossie

everything is OK now. Didn't know that AdvancedFilter needed a header
row.
I also tried the syntax "With Worksheets..." but omitted the dot
before "Cells".

Learned my lesson

Cheers,
Nash