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

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.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Alison" wrote:

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