Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|