CODEWITHSUNDEEP
vbaexcel
Amy
Amy

Reputation: 27

VBA changing the date to first day of month

Each week we receive a row of new data which is added to the top of the list of data (row 5). We only keep the most recent row of data from each month and delete the row below unless it's the next month example of spreadsheet (In the image I need to delete the row from April underneath). The problem is the way the date is formatted as it's dd/mm/yyyy which means if the day doesn't match the previous row, then it won't delete, even if the month is the same.

So far I've got the code to insert a new row in row 5, I've attempted to change the date to the 1st of the month (with an 'invalid procedure or call' error), and I've got an IF statement for if the dates match then it'll delete row 6. 

Worksheets("Weekly Ageing Summary").Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Worksheets("Weekly Ageing Summary").range("B5").Value = DateSerial(Year(Cells("B5")), Month(Cells("B5")), 1)

If Worksheets("Weekly Ageing Summary").range("B5").Value = Worksheets("Weekly Ageing Summary").range("B6").Value Then
    Rows(6).EntireRow.Delete
End If

This probably isn't the best logic to write the code but I'm just new to VBA and am unsure how else to do this.

Thanks in advance :)

Upvotes: 0

Views: 3305

Answers (1)

RobP
RobP

Reputation: 706

I tried your example code and was getting the same error as you - I think the problem is the way you are doing your cell references (Cells("B5") instead of Cells(5, 2)).

Try this:

Worksheets("Weekly Ageing Summary").Range("B5").Value = DateSerial(Year(Cells(5, 2).Value), Month(Cells(5, 2).Value), 1)

If Worksheets("Weekly Ageing Summary").Range("B5").Value = Worksheets("Weekly Ageing Summary").Range("B6").Value Then
    Rows(6).EntireRow.Delete
End If

Hope this helps!

Upvotes: 2

Related Questions