macro to delete rows
 

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

macro to delete rows
 

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

macro to delete rows
 

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

Patrick wrote in message
...
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

macro to delete rows
 

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.

--
Regards,
Tom Ogilvy


"Loomah" <bellm AT globalnet dot co dot uk wrote in message
...
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

Patrick wrote in message
...
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

macro to delete rows
 

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

Tom Ogilvy wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Loomah" <bellm AT globalnet dot co dot uk wrote in message
...
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

Patrick wrote in message
...
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

macro to delete rows
 

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?

"Loomah" wrote:

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

Tom Ogilvy wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Loomah" <bellm AT globalnet dot co dot uk wrote in message
...
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

Patrick wrote in message
...
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

macro to delete rows
 

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

"Patrick" wrote in message
...
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?

"Loomah" wrote:

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

Tom Ogilvy wrote in message
...
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.

--
Regards,
Tom Ogilvy


"Loomah" <bellm AT globalnet dot co dot uk wrote in message
...
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

Patrick wrote in message
...
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