Deleting rows older than 3 months

[towely321]

I want to write a macro that deletes entire rows with data older than 3 months from the last entry. So if the data contains mar, apr, may, jun data, the macro will delete all the January rows. The dates are in column G but are in mmm-yy format. Would it be better to assign number values for each month and compare them that way with a couple exceptions when it's at the end of the year going on to the next year? Or are there Date functions I could work with?

Thanks in advance.

[mancubus]

if you are familiar with recording a macro, record a macro while manuallly doing all your requirement.

then post the recorded macro, preferably with your workbook, here for fine tuning.

[Paul_Hossler]

I want to write a macro that deletes entire rows with data older than 3 months from

Also, how are you defining '3 months'?

90 days? (e.g. Newest = 6/15/20115, so only keep dates after 3/15/2015)

Any month farther away by name for the name of the newest month? (e.g. Newest = 6/15/20115, so only keep dates after Mar 1)

The col G dates might be displayed as mmm-yy, but internally (if it is really an Excel date) all the pieces are there

[towely321]

I just realized I messed up when I was first explaining it. I only want the last 3 months to be displayed. If the data contains March, April, May and June data, I want all of the rows from March to be deleted. If the data contains Nov 15, Dec 15 , Jan 16 and Feb 16 data, I want all of the rows from november 15 to be deleted. The dates are dates, just formatted to display as mmm-yy

I have this so far:


Dim d As Date
Dim hoja As Worksheet
Dim lastRow As Long

Set hoja = ThisWorkbook.Worksheets("Libro")
lastRow = hoja.Cells(Rows.Count, "G").End(xlUp).Row

d = hoja.Range("G" & lastRow).Value
For lastRow = lastRow To 1 Step -1
If hoja.Range("G" & lastRow).DateDiff("m", d, Date) > 3 Then .entirerow.delete
End If
Next lastRow

[SamT]

If DateDiff("m", hoja.Range("G" & lastRow), d) > 3 Then
Rows(LastRow).Delete
End If
Or

If DateDiff("m", hoja.Range("G" & lastRow), d) > 3 Then Rows(LastRow).Delete
Next LastRow

But those won't work because there are only 3 monthly time intervals in 3 months + (a month - 1 day.)
IOW, there are only 3 "m"s between 7/1/2015 and 10/31/2015.



(Not Compiled for testing)

Sub Towely()
Dim d As Date
Dim lastRow As Long
Dim Dif As Long

With ThisWorkbook.Worksheets("Libro")
lastRow = .Cells(Rows.Count, "G").End(xlUp).Row

d =.Range("G" & lastRow).Value
Dif = DateDiff("d", DateAdd("m", -3, d), d)

For lastRow = lastRow To 1 Step -1
If d - Dif > .Range("G" & lastRow) Then .Rows(lastRow).EntireRow.Delete
Next lastRow
End With
End Sub

[towely321]

[QUOTE=SamT;331719]
If DateDiff("m", hoja.Range("G" & lastRow), d) > 3 Then
Rows(LastRow).Delete
End If


I went with this code and just changed 3 to 2 and it did what I wanted. Thanks a lot!