I need help with Advanced Filter problem

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
I have a macro that uses the "Filter/Advanced Filter" function to first filter out all blank rows (no entry in column headed by "Fund"), then prints the sheet, then uses the "Filter/Advanced Filter" function to re-display all the blank rows.

My criteria for the first filter works fine and is cells A1:A2 and looks like:

Fund
>""

My criteria for the re-display filter bombs out. The macro doesn't display an error message, it just doesn't re-display the blank rows. The criteria is cells C1:D3 and looks like:

Fund Fund
>""
..........=""

What am I doing wrong?

_________________
I used to have delusions that I was a werewolf, but I'm much better noOOOWWWWWWWW!
This message was edited by Barry Katcher on 2002-09-19 10:58
This message was edited by Barry Katcher on 2002-09-19 11:24
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
Hey Barry !

is the actual code easy to post up ? it might a coding problem as oppose to the criteria.....
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Here you go, Chris:Sub Macro3()
Range("FilterRange").Select
Range("FilterRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Criteria").Range("Filter"), Unique:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("FilterRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Criteria").Range("UnFilter"), Unique:=False
End Sub


"FilterRange" is the range of the worksheet I want to filter, "Filter" is the name of the first criteria, in worksheet "Criteria" and "UnFilter is the name of the second criteria to re-display.

Incidentally, it bombs out the same way when, instead of using the macro, I manually use Filter/Advanced Filter, so I don't think it's the macro. It seems "greater than null" (>"") works fine both times but "equal to null" (="") doesn't work.

_________________
I used to have delusions that I was a werewolf, but I'm much better noOOOWWWWWWWW!
This message was edited by Barry Katcher on 2002-09-19 11:33
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
="" is the criteria to display empty text strings

To display nonblank rows use...

<>

to display blank rows use...

=
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791

ADVERTISEMENT

It sounds like you want to undo the filter, if this is correct:

ActiveSheet.AutoFilterMode = False
Activesheet.Showalldata
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Chris & Mark,

I replace the last ="" with just = and it works great.

Thanx, guys.
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Jim, ShowAllData would have been the ideal code to use, except that this is in a password protected worksheet being used by data-entry people. If I use ShowAllData, it asks for a password to unprotect the sheet. If I give out the password it would defeat the whole purpose of protecting it. I don't know of any way to use a password in a macro.
 

Forum statistics

Threads
1,143,617
Messages
5,719,735
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top