auto delete data at the start of every month

[adityatandel]

Hi,

I have a column with data which is connected to dates in another column. the numbers column gets filled as the days progress in the month. how do i get this column to recognize the start of a new month and delete the data of the previous month?

Thanks,
Adi

 

[HalfAce]

Hello adityatandel,
Assuming:
(1) The worksheet in question is named Sheet1
(2) The column to get cleared is column B
(3) You have headers in row 1 of Sheet1
(4) Your users will always have their security setting to allow either (automatically or manually)
macros to be enabled. . .

You can try this in your ThisWorkbook module.

Private Sub Workbook_Open()
If Day(Now) = 1 Then
  Dim LstRw As Long
  LstRw = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
  If LstRw >= 3 Then
    Sheets("Sheet1").Range("B2:B" & LstRw).ClearContents
  End If
End If
End Sub

Hope it helps.