VB question
 

I have a spreadsheet that has a column titled "Trans Code". This column is
one of 9 in the spreadsheet and is populated with one of the following, F800,
F900, F805 and F905. I want to delete all rows with F900, F805 and F905 and
keep only rows with F800 in that column. Can I accomplish this with VB code.
Also I only want to keep rows with todays date, I'm sure I can accomplish
this but just now sure how in Excel. Thank you.

VB question
 

Try this

Sub FilterData()
Dim iLastRow As Long
Dim iLastCol As Long
Dim iTrans As Long, iDate As Long
Dim rng As Range

iTrans = Application.Match("Trans Code", Rows(1), 0)
iDate = Application.Match("Date", Rows(1), 0)
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Columns(1).Insert
Set rng = Range("A2").Resize(iLastRow)
Range("A2").FormulaR1C1 = "=OR(RC" & iDate + 1 & "=TODAY(),RC" & _
iTrans + 1 & "=""F800"")"
Range("A2").AutoFill Range("A2").Resize(iLastRow - 1)
rng.AutoFilter field:=1, Criteria1:=False
rng.SpecialCells(xlCellTypeVisible).EntireRow.Dele te
Columns(1).Delete
End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"JOUIOUI" wrote in message
...
I have a spreadsheet that has a column titled "Trans Code". This column
is
one of 9 in the spreadsheet and is populated with one of the following,
F800,
F900, F805 and F905. I want to delete all rows with F900, F805 and F905
and
keep only rows with F800 in that column. Can I accomplish this with VB
code.
Also I only want to keep rows with todays date, I'm sure I can accomplish
this but just now sure how in Excel. Thank you.

VB question
 

Thanks so much for your assistance Bob....the code runs fine until it gets to
this part of the code:

Range("A2").AutoFill Range("A2").Resize(iLastRow - 1)

I'm set to take a VBA class in 3 weeks but until then this is all way to
complex for me. Any help you can provide is appreciated.

Thanks
Joyce

"Bob Phillips" wrote:

Try this

Sub FilterData()
Dim iLastRow As Long
Dim iLastCol As Long
Dim iTrans As Long, iDate As Long
Dim rng As Range

iTrans = Application.Match("Trans Code", Rows(1), 0)
iDate = Application.Match("Date", Rows(1), 0)
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Columns(1).Insert
Set rng = Range("A2").Resize(iLastRow)
Range("A2").FormulaR1C1 = "=OR(RC" & iDate + 1 & "=TODAY(),RC" & _
iTrans + 1 & "=""F800"")"
Range("A2").AutoFill Range("A2").Resize(iLastRow - 1)
rng.AutoFilter field:=1, Criteria1:=False
rng.SpecialCells(xlCellTypeVisible).EntireRow.Dele te
Columns(1).Delete
End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"JOUIOUI" wrote in message
...
I have a spreadsheet that has a column titled "Trans Code". This column
is
one of 9 in the spreadsheet and is populated with one of the following,
F800,
F900, F805 and F905. I want to delete all rows with F900, F805 and F905
and
keep only rows with F800 in that column. Can I accomplish this with VB
code.
Also I only want to keep rows with todays date, I'm sure I can accomplish
this but just now sure how in Excel. Thank you.