CODEWITHSUNDEEP
vbaexcel
codenewb
codenewb

Reputation: 75

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

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?

Upvotes: 0

Views: 4169

Answers (2)

codenewb
codenewb

Reputation: 75

    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.

Upvotes: 1

legendjr
legendjr

Reputation: 276

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.

Upvotes: 0

Related Questions