Delete Data

[Techtrend]

There are more then 10000 rows of data,
for sample i have attached only few ROWS.

Need to delete the data
DELETE ALL TIME EXCEPT 9.15AM TO 3.30 PM

FOR EX In the Attached sheet
The first date 04/01/2010

04/01/2010 9:00
04/01/2010 15:45
04/01/2010 16:00
04/01/2010 16:15
04/01/2010 16:30

The entire Col data has to deleted for these times like wise
i need this to be replicated for other rows and dates ,have huge data for which i need to do this

Can you please help me how to go about doing this,
do let me know if you need any clarification.

Thanks

Narendra

▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[chirayu]

Sub deleter()

'Define Variables
Dim x As Variant 'Start time
Dim y As Variant 'End Time
Dim subcell As Variant

'Set Variables
x = "09:15:00"
y = "15:30:00"

'Loop through rows
Range("A2").Select
Do Until IsEmpty(ActiveCell)
    subcell = Format(ActiveCell, "hh:mm:ss")
    If subcell >= x And subcell <= y Then
        ActiveCell.Offset(1, 0).Select
    Else
        ActiveCell.EntireRow.Delete
    End If
Loop

End Sub

 

[Marc L]

chirayu, with a lot of rows, instead of deleting row by row within a loop
faster is clearing all at once just using Excel basics : a formula and a sort !​

Sub Demo()
     Application.ScreenUpdating = False
With Sheet1.Cells(1).CurrentRegion.Resize(, Sheet1.Cells(1).CurrentRegion.Columns.Count + 1).Columns
    .Item(.Count).Formula = "=OR(ROUND(A1-INT(A1)-""9:15 AM"",4)<0,ROUND(A1-INT(A1)-""3:30 PM"",4)>0)"
    .Sort .Cells(.Count), xlAscending, Header:=xlYes
     V = Application.Match(True, .Item(.Count), 0)
     If IsError(V) Then .Item(.Count).Clear Else Union(.Rows(V & ":" & .Rows.Count), .Item(.Count)).Clear
End With
     Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Techtrend]

Thanks a lot