Macro - delete 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 a value smaller than a certain 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("01/01/2013") Then
Cells(i, "A").EntireRow.Delete
End If
Next x
Next i
End Sub

I am receiving a Next without For error... can somebody help please?

score 5 · Accepted Answer · answered Jul 30 '14 at 11:06

This lends itself well to using the .AutoFilter property of a Range. The script below contains a comment for each step taken:

Option Explicit
Sub DeleteDateWithAutoFilter()

Dim MySheet As Worksheet, MyRange As Range
Dim LastRow As Long, LastCol As Long

'turn off alerts
Application.DisplayAlerts = False

'set references up-front
Set MySheet = ThisWorkbook.Worksheets("Sheet1")

'identify the last row in column A and the last col in row 1
'then assign a range to contain the full data "block"
With MySheet
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    LastCol = .Range("A" & .Columns.Count).End(xlToLeft).Column
    Set MyRange = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
End With

'apply autofilter to the range showing only dates
'older than january 1st, 2013, then deleting
'all the visible rows except the header
With MyRange
    .AutoFilter Field:=1, Criteria1:="<1/1/2013"
    .SpecialCells(xlCellTypeVisible).Offset(1, 0).Resize(.Rows.Count).Rows.Delete
End With

'turn off autofilter safely
With MySheet
    .AutoFilterMode = False
    If .FilterMode = True Then
        .ShowAllData
    End If
End With

'turn alerts back on
Application.DisplayAlerts = True

End Sub

Running this code on a simple example (on "Sheet1" in this picture) that looks like this:

start

Will delete all rows with a date older than 1/1/2013, giving you this result:

end

thanks! i made some changes to the code and it worked fine! thanks! — user3606198, Jul 31 '14 at 07:50
Great -- glad to hear that it worked out. If an answer suits you, please accept it at your convenience. Here's a super-quick explanation in the form of a screenshot: http://i.stack.imgur.com/uqJeW.png — Dan Wagner, Jul 31 '14 at 11:02

score 1 · Answer 2 · answered Jul 30 '14 at 11:43

To answer your question

I am receiving a Next without For error

The problem is you are trying to loop on i but you haven't opened a For i loop. When you indent the code below any code that invokes a Loop or condition (i.e. If) it becomes obvious

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("01/01/2013") Then
            Cells(i, "A").EntireRow.Delete 'i has no value so Cells(0, "A") is ??
        End If
    Next x
    Next i 'where is the For i = ... in this code?
End Sub

When writing code I try to:

Enter the end command immediately if it's needed. So type If...Then, hit [ENTER], type End If, hit [HOME], hit [ENTER], hit [UP ARROW] then [TAB] to the right place to write the conditional code so that anyone will be able to read and understand it easily.
Always use Option Explicit at the top of every module to force variable declarations.

a tip about deleting rows based on a condition If you start at the top and work down, every time you delete a row your counter will effectively move to the cell two rows below the row you deleted because the row immediately below the deleted row moves up (i.e. it is not tested at all).

The most efficient way is to loop up from the bottom or your rows:

Sub DELETEDATE()
Dim x As Long
    For x = [a1].SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
        Debug.Print Cells(x, "A").Value
        If CDate(Cells(x, "A")) < CDate("01/01/2013") Then
            Cells(x, "A").EntireRow.Delete 'changed i to x
        End If
    Next x
End Sub

This way, the next row you want to test has been preserved - you've only moved the row below up by 1 and you've tested that row earlier.

score 0 · Answer 3 · answered Jul 30 '14 at 10:03

Please try with this

Sub DELETEDATE()
Dim x As Long
last = Range("A65536").End(xlUp).Row
For x = 1 To last
    Debug.Print Cells(x, "A").Value
    check:
    If x <= last Then
        If Trim(CDate(Cells(x, "A"))) <= Trim(CDate("7/29/2013")) Then
            last = last - 1
            Cells(x, "A").EntireRow.Delete
            GoTo check
        End If
    End If
Next x
End Sub

score -1 · Answer 4 · answered Jul 30 '14 at 09:21

-1

You have an additional Next i for some reason in your code as highlighted by the debugger. Try the below:

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("01/01/2013") Then
Cells(i, "A").EntireRow.Delete
End If
Next x
End Sub

answered Jul 30 '14 at 09:21

Gareth

5,140
5
42
73

how come i is still remaining empty? – user3606198 Jul 30 '14 at 09:23
because index is shifting when deleting rows. You need to [iterate backwards](http://stackoverflow.com/questions/19687018/what-does-the-to-and-step-mean-in-vba/19687126#19687126) – Jul 30 '14 at 09:25
how is the date format usually written in vba? cause i m having a type mismatch – user3606198 Jul 30 '14 at 09:27

Macro - delete rows based on date

4 Answers4

Linked