Help with deleting rows according to time value

**vpnvipin** · 07-02-2012, 05:19 AM

Hi all ,

I am struck with a problem.. I have a sheet which generate a data of machines which runned over a day ,, I dont want the data which ran on mid night means 12.00 am to morning 8.00 am
i want to delete those datas.

please help

Thanks in advance

excel_time.xlsx

**arlu1201** · 07-02-2012, 05:52 AM

Try this code on your test data first -

Option Explicit

Sub del_rows()
Dim lrow As Long, i As Long

With Worksheets("Sheet1")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = lrow To 2 Step -1
        If Hour(.Range("B" & i).Value) >= 0 And Hour(.Range("B" & i).Value) < 8 Then
            .Rows(i).Delete
            lrow = lrow - 1
        End If
    Next i
End With
End Sub

Put the code in a standard module.

**nilem** · 07-02-2012, 06:02 AM

or

Sub ertert()
Application.ScreenUpdating = False
Columns(1).Insert: Range("A1") = "temp"
Range("A2:A" & Cells(Rows.Count, 2).End(xlUp).Row).FormulaR1C1 = "=HOUR(RC[2])"
With Range("A1").CurrentRegion
    .AutoFilter Field:=1, Criteria1:="<8"
    .Offset(1).SpecialCells(12).EntireRow.Delete
    .AutoFilter
End With
Columns(1).Delete
Application.ScreenUpdating = True
End Sub

**vpnvipin** · 07-02-2012, 06:10 AM

Hi,
Thank you so much its working but its giving an error atlast

Run time error "13"
type mismatch

one more thing can u add one more condition which is " delte all the lines after 9pm " if possible . thank you so much rep added