macro to delete rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet that I use to import data from another application.
After the data is imported I would like to delete all rows that have a time
value of less than 0430 or greater than 0830. The time values are in a
column. Thanks for any help
 
Patrick,

Try the macro below. Assumes that your time values are in column A.

HTH,
Bernie
MS Excel MVP

Sub KeepCertainValues()
Dim myRows As Long
Range("A1").EntireColumn.Insert
Range("A1").FormulaR1C1 = _
"=IF(AND(RC[1]>=TIMEVALUE(""04:30:00"")," & _
"RC[1]<=TIMEVALUE(""08:30:00"")),""Keep"",""Trash"")"
myRows = ActiveSheet.UsedRange.Rows.Count
Range("A1").Copy Range("A1:A" & myRows)
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Trash", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A1").EntireColumn.Delete
End Sub
 
Patrick
Give this a shot. Assumes data starts in A1 and times are in column 2 (B)

With Range("A1").CurrentRegion
.AutoFilter Field:=2, Criteria1:="<04:30", _
Operator:=xlOr, Criteria2:=">08:30"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
 
Just a thought.
wouldn't that delete the header row if it existed and it if didn't, it would
delete the first row regardless of the time for that row.

You might want to add an offset in there.
 
You know Tom, you're right!
and until this point I couldn't quite workout how to do it but I did for my
own good and anybody else who's watching

With Range("A1").CurrentRegion
.AutoFilter Field:=2, Criteria1:="<04:30", _
Operator:=xlOr, Criteria2:=">08:30"
.Offset(1, 0).Resize(.Rows.Count - 1). _
SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

Fridays!
;-)
 
Thanks for the help this worked! However, I have another question related to
the same thing. Is it possible to do this using multiple criteria. Example if
I wish to delete rows that contain "x" or "y" or "z" or beginnining with the
letter "L" in a column?
 
Patrick,

The same general appoach works - simply record the filtering steps, though
you may need to use multiple filter applications, since you can only apply
two at a time.

HTH,
Bernie
MS Excel MVP
 
Back
Top