Delete Rows Prior to Last Month

[Alison]

Hi
This macro should delete rows from an imported txt for all entries that are
prior to last month. It is not doing this. How does the macro know what the
current date is in order to keep all entries for the month prior, and to
delete all other entries.

I know zero about VBA and have copied the macro below.


Delete rows prior to last month
Sub DeleteRowsPriorLastMonth()
Dim LResult As String
Range("U1").Select ' may have to revert to T2
Do While Trim(ActiveCell.Value) <> ""
Application.StatusBar = "Deleting SIFT rows where TXN_DATE older than
last month - count: " & g_lngDeletedRowCount
'job 1 - Replace dots with dashes in the TXN_DATE column
LResult = Replace(ActiveCell.Offset(1, 0).Value, ".", "/")
ActiveCell.Offset(1, 0).Value = Trim(LResult)
' Delete row
If Month(ActiveCell.Offset(1, 0).Value) < Month(Date) - 1 Then
ActiveCell.Offset(1, 0).EntireRow.Delete
g_lngDeletedRowCount = g_lngDeletedRowCount + 1 'count deleted
row
Else: ActiveCell.Offset(1, 0).Select 'count row not deleted
g_lngRowCount = g_lngRowCount + 1
End If
Loop
End Sub

Regards
Alison

 

[Shasur]

Can you try with datediff

For example,

datediff("m",Cdate(ActiveCell.Offset(1, 0).Value),Now)

Sub DeleteRowsPriorLastMonth()
Dim LResult As String
Range("U1").Select ' may have to revert to T2
Do While Trim(ActiveCell.Value) <> ""
Application.StatusBar = "Deleting SIFT rows where TXN_DATE older than
last month - count: " & g_lngDeletedRowCount
'job 1 - Replace dots with dashes in the TXN_DATE column
LResult = Replace(ActiveCell.Offset(1, 0).Value, ".", "/")
ActiveCell.Offset(1, 0).Value = Trim(LResult)
' Delete row
' More than Two months old
If DateDiff("m", CDate(ActiveCell.Offset(1, 0).Value), Now) > 2 Then
ActiveCell.Offset(1, 0).EntireRow.Delete
g_lngDeletedRowCount = g_lngDeletedRowCount + 1 'count deleted
Row
Else: ActiveCell.Offset(1, 0).Select 'count row not deleted
g_lngRowCount = g_lngRowCount + 1
End If
Loop
End Sub

Cheers
Shasur

 

[Alan Moseley]

Alison

The problem with the code is in this line:-
If Month(ActiveCell.Offset(1, 0).Value) < Month(Date) - 1 Then
This code will not work (for example) in February. You are wanting to
delete December transactions, but as month 12 is not less than month 1,
nothing gets deleted. As Shazur has pointed out you need to use the DateDiff
function to work out the number of months inbetween now and the transaction
date.

It's probably also worth looking at the lines of code which replace the dots
with slashes. I would think that these are going to cause problems, probably
changing what were UK dates to US.

 

[Alison]

Hi Shasur
I have copied the code as shown below and when I excute the macro it runs in
a continuous loop. What am I doing wrong?

Thanks for your patience
Alison

 

[Alison]

Hi Shasur
I have copied the code as shown below. When running the macro it cycles in
a continous loop. Any suggestions?
Thanks for your patience
Alison