Delete entire row based on date -excel VBA

Question

I am trying to delete all row where column A value(Its formatted as date) is less than today's date. I have to run these through entire non empty A column. but facing an issue with the code to run as loop through entire rows. each time its deleting only 1 row. Please let me know how to run it through entire row set.

Sub DeleteRowBasedOnDateRange()

Dim spem As Workbook
Dim ws As Worksheet
Dim N As Long, I As Long

Set spem = Excel.Workbooks("SwitchP.xlsm")
Set ws = spem.Worksheets("data")

N = ws.Cells(Rows.count, "A").End(xlUp).row

For I = 2 To N

    If Cells(I, "A").Value < Date Then

    Cells(I, "A").EntireRow.Delete

I = I + 1

    End If

Next I

End Sub

Scott Craner · Accepted Answer · 2018-02-09 14:50:53Z

5

Quick fix

Loop backwards.

Also you do not need the I=I+1 as that is done automatically.

Sub DeleteRowBasedOnDateRange()

Dim spem As Workbook
Dim ws As Worksheet
Dim N As Long, I As Long

Set spem = Excel.Workbooks("SwitchP.xlsm")
Set ws = spem.Worksheets("data")

N = ws.Cells(ws.Rows.count, "A").End(xlUp).row

For I = N to 2 Step -1    
    If ws.Cells(I, "A").Value < Date Then    
        ws.Rows(I).Delete
    End If    
Next I

End Sub

edited Feb 9, 2018 at 14:50

answered Feb 9, 2018 at 14:37

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Scott Craner Over a year ago

@K.Dᴀᴠɪs yes, as for good measure I should, I will change, but does it really matter? It would only matter if the active workbook was an .xslx and the workbook that the code is running against is an .xls. So I usually do not include it. Since I only use .xlsx and since it is returning a number and not an actual cell reference it does not matter against which sheet it pull that number.

K.Dᴀᴠɪs Over a year ago

I'm not sure. I figured Rows.Count would be counting the activesheet, whereas ws.Cells() would be referencing ws. I guess I've never tested it however and I cannot really confirm either way.

Scott Craner Over a year ago

@K.Dᴀᴠɪs It is counting the active sheet and returning a number, that number does not carry with it a parent sheet, it is only a number. As long as the workbook against which this code is being run, and in this case we know it to be an .xlsm, is later than 2007 then it does not matter from which sheet the Rows.Count is retrieved as they are all the same or, if it is gotten form a 2007 or prior, less. In this case and most cases it will not matter. Though, it is good practice here, because future viewer may still be working in 2007. In my own, I forgo it as unneeded repetitive typing.

K.Dᴀᴠɪs Over a year ago

That's right - I just re-realized that Rows.Count is counting the total rows of the sheet regardless of if 1 row is used or all 1,048,576. We can simply enter ws.Cells(1,048,576, "A"). I was under the impression for a moment that statement served a different purpose. It's time for more coffee! :-). Thanks for keeping me straight.

Collectives™ on Stack Overflow

Delete entire row based on date -excel VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related