Autofilter Range bug?

[StephenR]

I had problems while answering a question here yesterday, and attach a sample which illustrates it. I wonder if anyone here can explain it or provide a workaround (in the end I chose a different approach).

There are two columns of data and I apply a filter to show 1s in column A. The autofilter range is defined as the corresponding entries in column B. There are two 1s in A (A4 and A8) so the filter range should be B4 and B8. If I copy the range to sheet2 it works. If I return the address, it works (returns B4 and B8). But if I return the values, it returns the first value correctly (that in B4) but the second value returned is that in B5, not B8. Presumably because it is the next cell after B4 and it can't handle a non-contiguous range (but then why does the address work correctly?).

Please Login or Register  to view this content.

[romperstomper]

It's a 'problem' with the Cells property of a multi-area range. If you use Cells(n, 1) you will get the cell n-1 rows below the first cell in the range, even if that cell is not part of the range. You need to use a

Please Login or Register  to view this content.

loop to iterate through each cell, or loop through each area and then through each cell in that area.

[StephenR]

romperstomper: many thanks for the explanation and solution.