Excel: Use VBA to delete rows within a specified date range

Question

I have a workbook with ~20 sheets. In each sheet Column A has dates and Column B has data points. Column A is not the same in every sheet! I want to cut out data I don't need based on date ranges. I've tried this, and it runs for quite a long time, but does nothing.

Sub DeleteRowBasedOnDateRange()

Dim RowToTest As Long
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

For RowToTest = ws.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1

With ws.Cells(RowToTest, 1)
    If .Value > #6/16/2015# _
    And .Value < #6/22/2015# _
    Then _
    ws.Rows(RowToTest).EntireRow.Delete
End With

Next RowToTest

Next

End Sub

Suggestions?

This code worked fine for me. You say the date of each sheet is in a different format. Maybe you need to try to fix the format before running the macro as it might not be looked at as a date. — bbishopca
– bbishopca, Commented Jul 31, 2015 at 20:21
The formats are the same, but the actual dates/times agent identical... — codenewb
– codenewb, Commented Aug 1, 2015 at 23:38

codenewb · Accepted Answer · 2015-08-02 14:11:33Z

    Sub DeleteRowBasedOnDateRange()

Dim RowToTest As Long
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets

For RowToTest = ws.Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1

With ws.Cells(RowToTest, 1)
    If .Value < #6/16/2015# _
    Or .Value > #6/22/2015# _
    Then _
    ws.Rows(RowToTest).EntireRow.Delete
End With

Next RowToTest

Next

End Sub

This code worked fine for me. You say the date of each sheet is in a different format. Maybe you need to try to fix the format before running the macro as it might not be looked at as a date. – bbishopca

bbshopca you were right! It does work. It turns out I had my logic all backwards. I wanted to delete dates OUTSIDE the range of 2015-06-16 to 2015-06-22, not within. Since I have so many rows of data, I would see that the dates before 2015-06-16 weren't being deleted and thought my code wasn't working. Thanks for the input all.

legendjr · Accepted Answer · 2015-07-31 20:34:39Z

0

To speed it up, rather than delete one row at a time, you could sort the column by date, then find the rows within that range by using cells.find. Save those rows, then delete the range of rows at once. By doing it this way it's less brute force and it only requires finding 2 cells, and deleting rows once.

answered Jul 31, 2015 at 20:34

legendjr

2761 silver badge12 bronze badges

1 Comment

codenewb Over a year ago

How would you do that? Find the cell with the closest match for each of the two dates, then save the range from one to another?

Collectives™ on Stack Overflow

Excel: Use VBA to delete rows within a specified date range

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related