Solved

AdvancedFilter on Range object appears not to function

Posted on 2014-09-17
12
99 Views
Last Modified: 2014-09-18
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

0
Comment
Question by:krakatoa
  • 6
  • 6
12 Comments
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40327952
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
 
LVL 16

Author Comment

by:krakatoa
ID: 40327963
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40330010
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40330018
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
 
LVL 16

Author Comment

by:krakatoa
ID: 40330048
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
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40330090
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 16

Author Comment

by:krakatoa
ID: 40330128
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40330330
Have you disabled calculation and screen updating before applying filter?
0
 
LVL 16

Author Comment

by:krakatoa
ID: 40330430
Yes, I have. Doesn't affect the speed. Thanks.
0
 
LVL 16

Author Comment

by:krakatoa
ID: 40330778
Funny - my spreadsheet is for bookkeeping / accounts, and I see from your profile that you are in that area.

Thanks for your help.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40330866
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
 
LVL 16

Author Comment

by:krakatoa
ID: 40330904
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now