VB question

[Guest]

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.

 

[Bob Phillips]

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.Delete
Columns(1).Delete
End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

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