I tried to write VBA code to do the folowing on sheet PLAccounts.
I need to clear all the data from A12 to the last row containing data in Col W, except those items where the end of month date in Col T is one month prior to the Date in R5. for e.g. if date in R5 is 30/04/2023 then data that contains 31/03/2023 in the same row as Col T must not be cleared (Col A to W). I have highlighted the date NOT to be cleared in Yellow
See link below to my sample data
I need to clear all the data from A12 to the last row containing data in Col W, except those items where the end of month date in Col T is one month prior to the Date in R5. for e.g. if date in R5 is 30/04/2023 then data that contains 31/03/2023 in the same row as Col T must not be cleared (Col A to W). I have highlighted the date NOT to be cleared in Yellow
Code:
Sub Clear_PL_Data()
Dim lastRow As Long
Dim dateToKeep As Date
'get the last row containing data in column W
lastRow = Sheets("PLAccounts").Cells(Rows.Count, "W").End(xlUp).Row
'get the date to keep based on R5 cell value
dateToKeep = DateSerial(Year(Range("R5")), Month(Range("R5")) - 1, Day(Range("R5")))
'loop through each row and clear if required
For i = 12 To lastRow
If Range("T" & i).Value <> dateToKeep Then
Range("A" & i & ":W" & i).ClearContents
End If
Next i
End Sub
See link below to my sample data
Dropbox
www.dropbox.com