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!

--
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)



"Bill Foley" <pttincatitexasdotnet wrote in message
...
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!

--
Bill

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

"Bob Phillips" wrote in message
...
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" <pttincatitexasdotnet wrote in message
...
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!

--
Bill

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)

"Bill Foley" <pttincatitexasdotnet wrote in message
...
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

"Bob Phillips" wrote in message
...
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" <pttincatitexasdotnet wrote in message
...
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!

--
Bill

Perfect! Thanks...

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

--
Bill

"Bob Phillips" wrote in message
...
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)

"Bill Foley" <pttincatitexasdotnet wrote in message
...
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

"Bob Phillips" wrote in message
...
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" <pttincatitexasdotnet wrote in message
...
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!

--
Bill

You book the flight, I will be there <bg

"Bill Foley" <pttincatitexasdotnet wrote in message
...
Perfect! Thanks...

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

--
Bill

"Bob Phillips" wrote in message
...
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)

"Bill Foley" <pttincatitexasdotnet wrote in message
...
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

"Bob Phillips" wrote in message
...
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" <pttincatitexasdotnet wrote in message
...
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!

--
Bill