Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

AdvancedFilter on Range object appears not to function

Posted on 2014-09-17
12
Medium Priority
?
106 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

722 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