Excel VBA Deleting rows based upon date

Question

I have a question about the code in this thread

In that thread, is this code

    Sub ClearOldData()
    Application.ScreenUpdating = False
    Dimi As Long

    With Sheets("Data")

        For i = 2 To LastRow Step -1
            If Int(Date - Cells(i, "F").Value) > 179 Then
                .Rows(i).Delete
            End If
        Next i

    End With
    Application.ScreenUpdating = True
End Sub

The problem I am running into is that when I run this code, I receive the following error:

Compile error: Statement invalid outside Type block

Upon first look, the Dim looks to be incorrect as it is listed as Dimi and I can find no reference to VBA using something like that. I changed it to Dim and it still gives the error. This code appears to be correct in all I have researched on this, but I am scratching my head over where the hiccup is at. Anyone have an idea where it is going sideways at?

Thanks

Where in this Sub do you define and Set LastRow ? Also, fully qualify Cells(i, "F").Value by adding a . like .Cells(i, "F").Value. I would recommned to switch to If DateDiff("d", .Range("F" & i).Value, Date) > 179 Then — Shai Rado
– Shai Rado, Commented Mar 30, 2017 at 13:12
Two things : 'Dim i as long' (I believe it was a typo in the original post), and as is noted in the post itself: try to avoid deleting your rows on the fly, push every row to be deleted into an array THEN delete each row in the array (starting from the highest value down) to avoid shifting issues. — Aleksandar Misich
– Aleksandar Misich, Commented Mar 30, 2017 at 13:14
As this worksheet lines will flucuate, I figure that the last row after 180 days would be around row 3000. — RabidWookie
– RabidWookie, Commented Mar 30, 2017 at 13:22

Shai Rado · Accepted Answer · 2017-03-30 13:32:17Z

2

Try the code below, even with 1000 lines, it runs in less than 2 seconds (because you turn Application.ScreenUpdating = False)

Code

Option Explicit

Sub ClearOldData()

    Application.ScreenUpdating = False
    Dim i As Long
    Dim LastRow As Long

    With Sheets("Data")
        LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row ' <-- get last row with data from column F

        For i = LastRow To 2 Step -1 ' always loop backwards when deleting cells/ranges/rows
            If DateDiff("d", .Range("F" & i).Value, Date) > 179 Then ' older than 179 days
                .Rows(i).Delete
            End If
        Next i
    End With
    Application.ScreenUpdating = True

End Sub

answered Mar 30, 2017 at 13:32

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

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

4 Comments

RabidWookie Over a year ago

Thank You for the code! It is working, but when I run it, it will work and clear out the older lines, but it will then pop up with this error : Run Time Error '13': Type Mismatch and the debug then points to the DateDiff line and as far as I can tell it looks to be correct.

Shai Rado Over a year ago

@RabidWookie unless it's not form as date, is it ?

RabidWookie Over a year ago

It is just entered in as a date using the keyboard shortcut CTRL Semi Colon and is formated to show as a date (ie 3/30/2017). I did have to modify your code changing three variables. The sheet name changed from Data to Completed, the Range changed from F to G, and the days changed from 179 to 89

RabidWookie Over a year ago

I tried single stepping thru the code and when it gets to Next i, it loops back to the DateDiff. It will do that for 7 times and then gives me the error.

Dick Kusleika · Accepted Answer · 2017-03-30 14:12:26Z

Dimi As Long

Whoever wrote that meant to write Dim i As Long. If you write a Type statement, it looks like this

Public Type MyType
    TypeID As Long
    TypeName As String
End Type

See how those statements within the Type block look like variable declarations without the Dim keyword? That's the nature of the error message you're getting. The compiler sees a variable declaration for the variable Dimi and no Dim keyword and complains that you have type member declarations without a Type statement.

If you change it to Dim As Long you get the same error but instead of the compiler complaining about the variable Dimi, it's complaining about the variable Dim. While Dim is not a valid variable name, it is a valid type member name.

@Shai Rado gives you working code with things like Option Explicit, looping backwards when you're deleting rows, and the requirement to loop from a higher number to a lower number when your step is negative, so I won't repeat those here. This answer just explains why you got the error you did.

Collectives™ on Stack Overflow

Excel VBA Deleting rows based upon date

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related