Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

AdvancedFilter on Range object appears not to function

Posted on 2014-09-17
12
Medium Priority
?
107 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 34

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 17

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 34

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 34

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 17

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 34

Accepted Solution

by:
Rob Henson earned 2000 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
 
LVL 17

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 34

Expert Comment

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

Author Comment

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

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 34

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 17

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

824 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