# I need help with Advanced Filter problem

#### Barry Katcher

##### Well-known Member
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hey Barry !

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

Here you go, Chris:Sub Macro3()
Range("FilterRange").Select
Sheets("Criteria").Range("Filter"), Unique:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
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

="" is the criteria to display empty text strings

To display nonblank rows use...

<>

to display blank rows use...

=

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

ActiveSheet.AutoFilterMode = False
Activesheet.Showalldata

Chris & Mark,

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

Thanx, guys.

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.

Replies
8
Views
403
Replies
18
Views
1K
Replies
15
Views
710
Replies
1
Views
736
Replies
0
Views
420

1,219,998
Messages
6,151,379
Members
451,024
Latest member
saotome1

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

### Which adblocker are you using?

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

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