How to delete data which are older than current month.

[Rajvagadia]

Hello Team,

Need your help in deleting rows which are older than current month. I have written below Macro and I am able to delete data which are prior to 30 days old. Please advise how to write Macro for the data which will delete all old data except current month. Thank you.

Sub DeleteOlder()
Dim rng As Range
Dim cel As Range
Dim m As Long
B = Range("B" & Rows.Count).End(xlUp).Row
For Each cel In Range("B1:B" & B)
If cel.Value < Date - 30 Then
If rng Is Nothing Then
Set rng = cel
Else
Set rng = Union(rng, cel)
End If
End If
Next cel
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
End Sub

 

[Joe4]

Welcome to the Board!

Try this:

Sub DeleteOlder2()

    Dim myRow As Long
    Dim lastRow As Long
    
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    For myRow = lastRow To 1 Step -1
        If Format(Date, "yyyymm") <> Format(Cells(myRow, "B"), "yyyymm") Then
            Rows(myRow).Delete
        End If
    Next myRow
    
End Sub

 

[Rajvagadia]

Hi Joe, Macro is not working. Entire sheet is getting deleted. Please review once.

 

[Joe4]

Did you copy my Macro "as-is", or try typing it yourself?

Your date data exists in column B starting at row 1, right?
I entered 10 dates in cells B1:B10. Three had dates from August 2016 and the rest were for other months & years.
It correctly deleted everything except those three from August 2016.

 

[Rajvagadia]

Hi Joe, Thank you very much for your quick reply. However I am new to VBA. I have recently started my VBA training a week back so just trying to put some effort in Office. I am sorry for the trouble. But Can you please advise how to give range B2 to B2000 as I am trying to delete its deleting entire data. Could you please help. Sorry for the trouble Joe.

 

[Joe4]

This line here:

[COLOR=#333333]lastRow = Range("B" & Rows.Count).End(xlUp).Row[/COLOR]

automatically finds the last row in column B with data.

If you want to do an explicit range of rows 2 to 2000, you could just change this line:

For myRow = lastRow To 1 Step -1

to this:

For myRow = 2000 To 2 Step -1

If you really have dates within the current month in column B, it should NOT delete them.
Unless your dates are really entered as text and not as dates.

What happens if you place this formula anywhere on your sheet?

=ISNUMBER(B2)

What does it return?

 

[mithi]

Hi,

what if we need to alter the above code to delete data older than two months, the above code only does all except the current month:
Sub DeleteOlder2()

Dim myRow As Long
Dim lastRow As Long

lastRow = Range("B" & Rows.Count).End(xlUp).Row
For myRow = lastRow To 1 Step -1
If Format(Date, "yyyymm") <> Format(Cells(myRow, "B"), "yyyymm") Then
Rows(myRow).Delete
End If
Next myRow

End Sub

 

[Joe4]

Welcome to the Board!

Try this:

Sub DeleteOlder2()

    Dim myRow As Long
    Dim lastRow As Long

    Application.ScreenUpdating = False

    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    For myRow = lastRow To 1 Step -1
        If DateDiff("m", Cells(myRow, "B"), Date) > 2 Then Rows(myRow).Delete
    Next myRow

    Application.ScreenUpdating = True
    
End Sub