For loop using dates Compile Error: Syntax Error

Question

I am trying to run a script that will skim through all rows for a certain column. Then it will compare the dates in these columns with a set date. If the dates is larger than it deletes the row. The error I'm getting is called Compile Error: Syntax Error.

Sub removewrongyear()
Dim i As Integer

For i = 2 To 635475
 If Data.Cells(i,20).Value > DATE(2018,12,31) Then
Rows(i).EntireRow.Delete
Next i
End Sub

What you're trying to do is better accomplished by autofilter. — JohnyL
– JohnyL, Commented May 16, 2018 at 20:09
@JohnyL Sorry Year A is the current year I determine in another part of script I will edit it to 2018 to not confuse. — user9730643
– user9730643, Commented May 16, 2018 at 20:10

Ryszard Jędraszyk · Accepted Answer · 2018-05-16 20:45:21Z

3

Yuo need to step backwards as @braX stated. Also specified number exceeds Integer variable type capacity. In VBA it is a good practice for ingeter values to always declare variable as Long.

Range "Data" is nowhere set. I replaced it with reference to active worksheet. Variable YearA is also not specified. I assigned value of 2018 to it. Date function is incorrectly used, you meant to use DateSerial.

Always put Option Explicit on top of the code to catch errors. There were really many here.

Option Explicit

Sub removeWrongYear()

    Dim i As Long, yearA as Long

    yearA = 2018

    With ActiveSheet
        For i = 635475 to 2 Step -1
            If .Cells(i,20).Value > DateSerial(yearA,12,31) Then .Rows(i).EntireRow.Delete       
        Next i
    End With

End Sub

Here is a fast version, based on arrays, with all rows deleted at once:

Option Explicit
Option Base 1 'row and column index will match array index

Sub removeWrongYear()

    Dim i As Long, yearA As Long, rowsCnt As Long
    Dim rowsToDelete As Range
    Dim vData As Variant

    yearA = 2018

    With ActiveSheet

        '1st to 635475 row, 20th column
        vData = Range(.Cells(1, 20), .Cells(635475, 20))

        For i = UBound(vData) To 2 Step -1
            If vData(i, 1) > DateSerial(yearA, 12, 31) Then
                rowsCnt = rowsCnt + 1

                If rowsCnt > 1 Then
                    Set rowsToDelete = Union(rowsToDelete, .Rows(i))
                ElseIf rowsCnt = 1 Then
                    Set rowsToDelete = .Rows(i)
                End If

            End If
        Next i

    End With

    If rowsCnt > 0 Then
        Application.ScreenUpdating = False
        rowsToDelete.EntireRow.Delete
        Application.ScreenUpdating = True
    End If

End Sub

edited May 16, 2018 at 20:45

answered May 16, 2018 at 20:14

Ryszard Jędraszyk

2,4414 gold badges26 silver badges56 bronze badges

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

2 Comments

user9730643 Over a year ago

I am having an issue with comparing the dates as their format is different one is formatted as 27-JAN-18 and the other is 2017-12-31. Is there a way to convert these dates.

Ryszard Jędraszyk Over a year ago

You can put a break point near date comparison and see how format of compared dates looks like by hovering over. Normally in VBA everything what is date is comparable, because it represents Long value. Problem can arise when VBA does not recognize cell contents as date, but String. In such case depending on actual String, dedicated code should be written to convert it to Date.

Collectives™ on Stack Overflow

For loop using dates Compile Error: Syntax Error

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related