Finding a Cell that meets a condition

[hyoung3]

Have a data set that is very large. One field is populated with time values. I want to delete all rows up to a certain time value (ie. any row with time value < 11:00). I have used a while loop with an imbedded IF THEN statement that will delete rows as the condition is tested but it takes way too long. I was wondering if there was a way to find the address of the first cell that meets my condition and delete all rows that do not. Thanks.

 

[JackDanIce]

Welcome to the board.

I don't have time to provide the full code (as I'm about to go home!) but this is something I use a lot and saved in my personal folder:

Sub DeleteVisibleRows()
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
ActiveSheet.AutoFilterMode = False
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Assuming the data is already filtered, it deletes everything that is visible, except the header row; I've not included the filter part as it gives me the flexibility to use on any sheet when I filter and I don't know in advance which column I'm filtering by.

Perhaps you can apply a filter for all values less than 11:00 and then use above?

A slight change to above is to use the special cell types and visible cells only, a quick bit of searching online and you should be able to find this.

Good luck,
Jack

 

[hyoung3]

Thanks Jack!

That works well! I appreciate the quick response