Macro to delete multiple rows based on time of day

**managermonroe** · 06-11-2013, 06:03 PM

I am hoping that someone can help an extreme novice with making a new macro. I have a spreadsheet with times of day in column B. There is one time of day per cell (formated as HH:MM AM/PM) and the times are in chronological order starting with cell B4 and continuing on down. My problem is this, I am trying to write a macro that will search through column B and when it gets to a cell that has a time of day that is past 10:30 PM, it will delete that row and every following row that contains data because I only care about the times of day before 10:30 PM. Can anyone help me out?

**JBeaucaire** · 06-11-2013, 06:15 PM

You should be able to apply an AUTOFILTER to your data, apply a CUSTOM filter to show only values greater than 10:30pm, then delete all visible rows at once. Then turn off the autofilter. You should be able to record yourself doing that to create a macro that only needs a little tweaking to make it reusable. Do that, record the macro, then post it here and we'll help you tweak it.

Be sure to use CODE tags around posted code. (You read the Forum Rules, I know you did)

**managermonroe** · 06-12-2013, 01:20 PM

JBeaucaire, thank you for the suggestion. I tried what you said and it did work to some degree but there is a problem with deleting the entries based on filtering that row's time value. My problem is that the times I am working with in column B go from 6:00am on one day to 6:00 am of the following day. This information is generated in a scheduling program that I can export the data into an excel format. When the times go past midnight they are being represented as 0:01 AM instead of 12:01 AM and are thus not recognized as a real time value. The reason for this is that these cell entries have a "space" as their first character and when I delete it, the cell reverts to the correct time format of 12:01 AM. If you can help me with a way of deleting a "space" at the beginning of every cell entry in column B, your auto filter suggestion should work for me just fine.

**managermonroe** · 06-12-2013, 01:50 PM

It looks like the information I needed was already out there. The issue of getting rid of spaces at the beginning of every cell in a column was solved in this thread: http://www.mrexcel.com/forum/excel-q...rim-macro.html I will mark this issue as solved and thank you again for pointing me in the right direction with a simpler way to solve my problem.