Macro help required
 

I have set up my filters(datafilterauto filter)
Now I want a macro that will do the following:
By invoking the macro it will
1. Show only the blank cells on any given selected col
and by invoking the same macro will display ALL the entries in a selected
col

Would appreciate some help
Thanks

Macro help required
 

Hi, adapt the following....
where the filter is set to range A1:A5, the Field number relate to the
column# of the filtered column and the criteria the filter value.
The first use of autofilter turns it on (be careful if it is on, it turns
off!) and, the second filters blanks only, and the last resets the criteria
to ALL.


Sub Filter()
With Range("A1:A5")
.Autofilter
.AutoFilter Field:=1, Criteria1:="="
.AutoFilter Field:=1
End With
End Sub

--
Cheers
Nigel



"Ian Macfarlane" wrote in message
...
I have set up my filters(datafilterauto filter)
Now I want a macro that will do the following:
By invoking the macro it will
1. Show only the blank cells on any given selected col
and by invoking the same macro will display ALL the entries in a selected
col

Would appreciate some help
Thanks

Macro help required
 

Hi
Thnaks for the help....but
I implemented your code.
Running the code did place the filter in place...but that is all
Further running of the code did nothing.
If I cliked on the filter arrow and select "Blanks" all was well THEN if I
ran the code again, ALL the entries would show.
But I never could get the BLANKS only to show
any ideas?
Thansks for your help so far

"Nigel" wrote in message
...
Hi, adapt the following....
where the filter is set to range A1:A5, the Field number relate to the
column# of the filtered column and the criteria the filter value.
The first use of autofilter turns it on (be careful if it is on, it turns
off!) and, the second filters blanks only, and the last resets the
criteria
to ALL.


Sub Filter()
With Range("A1:A5")
.Autofilter
.AutoFilter Field:=1, Criteria1:="="
.AutoFilter Field:=1
End With
End Sub

--
Cheers
Nigel



"Ian Macfarlane" wrote in message
...
I have set up my filters(datafilterauto filter)
Now I want a macro that will do the following:
By invoking the macro it will
1. Show only the blank cells on any given selected col
and by invoking the same macro will display ALL the entries in a selected
col

Would appreciate some help
Thanks

Macro help required
 

possibly what you want to toggle between all and blanks

Sub Filter()
With Range("A1")
if activesheet.AutofilterMode = False then
.Autofilter
end if
if Activesheet.FilterMode = False then
.AutoFilter Field:=1, Criteria1:="="
Else
.AutoFilter Field:=1
End If
End With
End Sub

Assumes Headers start in Cell A1.


--
Regards,
Tom Ogilvy


"PCOR" wrote in message
...
Hi
Thnaks for the help....but
I implemented your code.
Running the code did place the filter in place...but that is all
Further running of the code did nothing.
If I cliked on the filter arrow and select "Blanks" all was well THEN if I
ran the code again, ALL the entries would show.
But I never could get the BLANKS only to show
any ideas?
Thansks for your help so far

"Nigel" wrote in message
...
Hi, adapt the following....
where the filter is set to range A1:A5, the Field number relate to the
column# of the filtered column and the criteria the filter value.
The first use of autofilter turns it on (be careful if it is on, it
turns
off!) and, the second filters blanks only, and the last resets the
criteria
to ALL.


Sub Filter()
With Range("A1:A5")
.Autofilter
.AutoFilter Field:=1, Criteria1:="="
.AutoFilter Field:=1
End With
End Sub

--
Cheers
Nigel



"Ian Macfarlane" wrote in message
...
I have set up my filters(datafilterauto filter)
Now I want a macro that will do the following:
By invoking the macro it will
1. Show only the blank cells on any given selected col
and by invoking the same macro will display ALL the entries in a
selected
col

Would appreciate some help
Thanks