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

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,595
Office Version
  1. 2021
Platform
  1. Windows
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

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

 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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:
VBA Code:
dateToKeep = DateSerial(Year(Range("R5")), Month(Range("R5")), 1) - 1

Try this:
VBA Code:
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:
VBA Code:
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
--------------
 
Upvote 0
Solution

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top