Macro to clear all data except where month end date is one month prior to report Date

[howard]

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

 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 - File Deleted - Simplify your life

www.dropbox.com

 

[DanteAmor]

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

If you mean the last day of the previous month, then:

dateToKeep = DateSerial(Year(Range("R5")), Month(Range("R5")), 1) - 1

Try this:

Sub Clear_PL_Data()
  Dim lastRow As Long
  Dim dateToKeep As Date
  Dim i As Long
  
  lastRow = Sheets("PLAccounts").Cells(Rows.Count, "T").End(xlUp).Row
  '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
  dateToKeep = DateSerial(Year(Range("R5")), Month(Range("R5")), 1) - 1
  For i = 12 To lastRow
    If Range("T" & i).Value <> dateToKeep Then
      Range("A" & i & ":W" & i).ClearContents
    End If
  Next i
End Sub

Or this:

Sub Clear_PL_Data_v2()
  Dim dateToKeep As Date
  dateToKeep = DateSerial(Year(Range("R5")), Month(Range("R5")), 1) - 1
  Range("A11:W" & Range("T" & Rows.Count).End(3).Row).AutoFilter 20, "<>" & Format(dateToKeep, "mm/dd/yyyy")
  ActiveSheet.AutoFilter.Range.Offset(1).ClearContents
  ActiveSheet.Range("A11").AutoFilter
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[howard]

Thanks Dante


I used your first code provided and it worked 100%