Solved

AdvancedFilter on Range object appears not to function

Posted on 2014-09-17
12
101 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 33

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 33

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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 33

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 33

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
 
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 33

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 33

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

809 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