Vba code for End Of Month

[Big Lar]

UserForm fills WS1 with a new row of numeric data every few days. WS ColumnA is “Date”.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Need help with code:<o></o>
On the first instance of a new month<o></o>
…delete all but the last row of data<o></o>
……clear contents of C2:CJ & M2:AJ2<o></o>

 

[venkat1926]

suppose your data is like this in sheet1 (copy sheet1 data in sheet 2 useful to retrieval)

hdng1 hdng2 hdng3 hdng4 hdng5 hdng6
1 48 77 40 39 43
2 2 71 21 90 96
3 34 24 70 72 25
4 94 31 31 78 1
5 95 79 56 30 48


now run this macro test




you will get only the last row.

your second point "clear contents of C2:CJ & M2:AJ2" is not clear.
if you want only the last row there can be only one row apart from the headings row.

Sub test()
Dim r As Range, r1 As Range
Worksheets("sheet1").Activate
Set r = Range("A1").CurrentRegion
Set r1 = r.Offset(1, 0).Resize(r.Rows.Count - 2, r.Columns.Count)
r1.EntireRow.Delete
MsgBox r.Address
End Sub

Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
End Sub

 

[Big Lar]

Thanks for the code, venkat1926

The second bit was miswritten. It was intended to clear the values of all cells in the newly positioned last row...except for Cells A2, B2, K2, and L2...it's just a little "housekeeping".

But, I'm still puzzled about how to automatically trigger the procedure on the 1st instance of a new month. In other words...

IF last date in ColumnA is 06/29/11 And Today is July 2 Then
venkat1926's code

Your help is appreciated.