Apply Auto Filter on One Column

[Bill Foley]

Hey Gang,

Excel 2003

Thanks to Max and Bob is the MISC group I was able to create a dummy column
and use it to filter three columns of data. What I am looking for is a
method to do this using VBA (since I'm lazy and want to assign a macro to a
button on a toolbar).

Basically what I need is VBA code to:

1. Turn on the AutoFilter (which I know how to do), and,
2. Apply a filter to Column "T" (actually T2:T1529), for every value that is
"True"

FYI - column "T" is looking for every record in columns "E, F, and G" that
is "N" (formula is: "=COUNTIF(E2:G2,"N")>0"). Reason I mention this is I
wouldn't mind being able to get rid of Column "T" (dummy column) and have
code that turns on the AutoFilter and filters all records where columns "E,
F, OR G" have "N". Either option would be much appreciated.

TIA!

 

[Bob Phillips]

Sub Macro2()
Dim rng As Range
Dim lastcell As Range
Dim ilastrow As Long

With ActiveSheet

Set lastcell = .Cells.Find("*")

ilastrow = .UsedRange.Cells(.UsedRange.Cells.Count).Row
Set rng = .Range("H2", "H" & ilastrow)
.Range("H1").Value = "Temp"
rng.Formula = "=COUNTIF(E2:G2,""N"")>0"
Range("H1").Resize(ilastrow).AutoFilter

End With
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bill Foley]

Bob,

Thanks (again) for the assistance. However, all this does is create a new
column (I changed it to column "U") with the exact same data that I got from
your filter formula earlier. It doesn't actually apply the filter to
"True", which is what I really want. Also, if the AutoFilter is already on
it just turns it off, so I added the code to test AutoFilter first.

Even though this is pretty cool stuff, if the data has to be there anyway
(which it is from a previous posting where you helped me), what I would
really like is VBA code to be able to:

1. Turn on the Autofilter
2. Apply the filter to column "T" for every value that is "TRUE" (this is
the part I can't figure out)

That way I can assign that macro to a toolbar button and have the "Show All"
button right next to it to remove the filter when I am done. FYI, I have a
lot of other columns with multiple filtering that I will need to modify this
for, that is why my "dummy" column was way over at "T". I could move this
column over to where it is easy to access, but then I end up with data that
I really don't want to see (if you know what I mean).

I sincerely do appreciate all your help (and anyone else who pipes in).

--
Bill

Sub Macro2()
Dim rng As Range
Dim lastcell As Range
Dim ilastrow As Long

With ActiveSheet

Set lastcell = .Cells.Find("*")

ilastrow = .UsedRange.Cells(.UsedRange.Cells.Count).Row
Set rng = .Range("H2", "H" & ilastrow)
.Range("H1").Value = "Temp"
rng.Formula = "=COUNTIF(E2:G2,""N"")>0"
Range("H1").Resize(ilastrow).AutoFilter

End With
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

 

[Bob Phillips]

Well when you said you were being lazy, I thought you wanted to do stuff. If
you call that lazy, I say pathetic, I could show you real lazy <BG>

Anyway,

Columns("U:U").AutoFilter Field:=1, Criteria1:="TRUE"


To remove the filter afterwards just use

Columns("U:U").AutoFilter

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob,

Thanks (again) for the assistance. However, all this does is create a new
column (I changed it to column "U") with the exact same data that I got
from your filter formula earlier. It doesn't actually apply the filter to
"True", which is what I really want. Also, if the AutoFilter is already
on it just turns it off, so I added the code to test AutoFilter first.

Even though this is pretty cool stuff, if the data has to be there anyway
(which it is from a previous posting where you helped me), what I would
really like is VBA code to be able to:

1. Turn on the Autofilter
2. Apply the filter to column "T" for every value that is "TRUE" (this is
the part I can't figure out)

That way I can assign that macro to a toolbar button and have the "Show
All" button right next to it to remove the filter when I am done. FYI, I
have a lot of other columns with multiple filtering that I will need to
modify this for, that is why my "dummy" column was way over at "T". I
could move this column over to where it is easy to access, but then I end
up with data that I really don't want to see (if you know what I mean).

I sincerely do appreciate all your help (and anyone else who pipes in).

 

[Bill Foley]

Perfect! Thanks...

Guess you got me pegged! Any chance you can come over and put the macro in
for me? HA!

 

[Bob Phillips]

You book the flight, I will be there <bg>

Perfect! Thanks...

Guess you got me pegged! Any chance you can come over and put the macro
in for me? HA!