AdvancedFilter on Range object appears not to function

I define a Range object; set it to the used cells of a particular column; then apply a Filter. But although there is no error on this code, the filter does not appear to have been applied. (If I just filter the entire column raw, then the filter works). Any thoughts ? Thanks.    

dim condensedRange as Range

    Set condensedRange  = WS.Range("A1", WS.Range("A1").End(xlDown).End(xlDown).End(xlUp))

    condensedRange .AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Open in new window

LVL 17
krakatoaAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
This wouldn't filter on the whole column. The cell reference is going to the bottom of the sheet and then going up to the bottom of the data. In much the same way as you combination of 2 x Down and 1 x Up will go to the bottom of the data, then the bottom of the sheet and then up to the bottom of the data.
0
 
Rob HensonFinance AnalystCommented:
Might be a dumb question but I assume there are duplicates in the list that should be filtered out. Your filter is filtering in place for Unique items. If all items only occur once then the appearance will be that the filter hasn't done anything.

Thanks
Rob H
0
 
krakatoaAuthor Commented:
Yes, no, not a dumb question.

There will or could be duplicate items for sure.

But I know that the filter is not working through the object approach, because the sheet's row structure has not been shrunk, whereas when I apply the filter to the entire column, it shows all the irrelevant rows collapsed.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Rob HensonFinance AnalystCommented:
What happens if you do away with the Variable definition?

This would then be:

  WS.Range("A1", WS.Range("A1").End(xlDown).End(xlDown).End(xlUp)).AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
I am assuming that you are using two lots of End(xlDown) and End(xlUp) in column A to get you to the bottom of the data.

When done manually does this happen? Alternative would be to just use:

WS.Range("A1", WS.Range("A1048576").End(xlUp))

Thanks
Rob
0
 
krakatoaAuthor Commented:
What happens if you do away with the Variable definition?

Well, it results in the same effect. So there is no difference.

Alternative would be to just use:

this is problematic, mainly because the point of my trying to apply the filter only to the used part of the column was to speed up the calculations, which are quite heavy. If the entire column is evaluated, this introduces a big performance hit, and I can see from the debugger that it is the execution of the filtering, on a mostly empty column, that takes the time.
0
 
krakatoaAuthor Commented:
Yes, you are right.

What is now apparent is that because of the header row impregnability, if there are two identical terms in the column and they are the only two terms in it (so occupying just cells A1 and A2 let's say), then the filter will not collapse subsequent cells, and so the row gui looks unchanged, although the status bar will show that only 1 record has been found - which is of course correct.

So this is essentially a 'phantom' problem of mine, brought about by seeing during debugging that the presentation - the collapsed 'blue-rows' look after a filter operation - is not there under this unique scenario. So the filtering - with or without a range object - does work.

The real problem still remains that the filtering seems to take a long time, and it is this that I was trying to optimise.

Thanks for you assistance - I will close the question after any other comment you want to make, but your responses did make me look again at what is going on, and that has helped narrow down the speed issue conundrum, on which I'll now have to focus.
0
 
Rob HensonFinance AnalystCommented:
Have you disabled calculation and screen updating before applying filter?
0
 
krakatoaAuthor Commented:
Yes, I have. Doesn't affect the speed. Thanks.
0
 
krakatoaAuthor Commented:
Funny - my spreadsheet is for bookkeeping / accounts, and I see from your profile that you are in that area.

Thanks for your help.
0
 
Rob HensonFinance AnalystCommented:
Indeed, I have my own Limited Company providng Project and Cost accounting for a major Defence Company. If only I could make my money doing this instead!!!
0
 
krakatoaAuthor Commented:
Right, well if you ever need to see a different kind of spreadsheet in action, then this may be of interest.

(It 'amounts' to a non-power-user "pivot table" that can be configured on the fly, without knowledge of pivots, macros, vba, or formulae). ;)
0
All Courses

From novice to tech pro — start learning today.