Deleting rows based on date

Question

I am very new to VBA and macros in Excel. I have a very large excel spreadsheet in which column A holds dates. I am trying to delete the rows which have only Today's Date and this is what I have come up with till now..

Sub DELETEDATE()
Dim x As Long
    For x = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
        Debug.Print Cells(x, "A").Value
        If CDate(Cells(x, "A")) < CDate("Now()") Then
            Cells(i, "A").EntireRow.Delete
        End If
    Next x
Next i
End Sub

Answer 1

There are a number of issues with your code. If you are new to VBA then you should put Option Explicit at the start of each Class/Form/Module so that you will get quicker feedback on errors you are making.

You should always qualify excel references by the Worksheet so that you are not working with the implicit active sheet e.g. you need myWb.Range rather than just Range. There are lots of examples if you google this topic.

You can use the macro recorder to record code to see what Excel is expecting in terms of usage. Code produced by the macro recorder is generally not very good code so only use it to clarify, not a style to aspire to.

You are also making a classic mistake when you come to delete a row, which is that you are changing the collection with which you are working.

VBA and Excel(or any other application) are two seperate entities. Neither knows what the other is doing unless they pass messages to each other through a set of well defined interfaces. These interfaces are provided by the application. E.g. you tell Excel you want to work on a range object by using the Range 'interface' etc.

When you look at your for loop you have told VBA that it is going to loop x number of time with x being a value obtained from excel.

VBA is given the final number for x but has absolutely no idea that this value is connected to a set of objects in Excel.

In your for loop you delete one of the rows in Excel. This means that all rows after the row you delete are renumbered to be one less than they were. But VBA doesn't know this, it just knows it is increasing x by 1 until it reaches the maximum value you set earlier.

The problem is, now that you have deleted a row, that that maximum value no longer represents the number of rows you are processing.

Even more horrible, the row that was e.g. row 4 is now row 3 because you deleted the old row 3, but x, which is currently 3 is going to be increment to 4, but the old row 4 is now row 3 so that x=4 is now looking at the old row 5 which is the new row 4. This means that every time you delete a row, you will skip over the row that was one more than the row you deleted.

Its easy to avoid this problem by counting down rather than counting up. So your for loop should be

For x = Cells.SpecialCells(xlCellTypeLastCell).Row to 1 step -1

You should aslo use variable names that mean something. In this case I'd suggest myRow rather than 'x'.

Two other good practises are

1. Always do a Debug.Compile project before trying to run your code. This will check the whole of your code for syntax errors.

2. Install the free and fantastic Rubberduck addin for VBA. You can use this addin after your code compiles cleanly. Use the Code Inspections to find out where you have made assumptions you didn't know you'd made.