I need help with Advanced Filter problem

[Barry Katcher]

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

 

[Chris Davison]

Hey Barry !

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

 

[Barry Katcher]

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

="" is the criteria to display empty text strings

To display nonblank rows use...

<>

to display blank rows use...

=

 

[Jim North]

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

ActiveSheet.AutoFilterMode = False
Activesheet.Showalldata

 

[Barry Katcher]

Chris & Mark,

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

Thanx, guys.

 

[Barry Katcher]

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.