Forum Discussion

heathermarie923's avatar
heathermarie923
Copper Contributor
Sep 13, 2022
Solved

Need help with VBA code to clear content of a range of cells based on today's date.

Good morning,

 

I am looking for help to formulate VBA Code.  

 

I need to clear the contents of Columns C:F if Column F is less than today's date.  Can anyone help me with this?  Please and thank you in advance!

 

Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Sep 13, 2022

    heathermarie923 

    Without clicking the button?

    Private Sub Worksheet_Activate()
Dim i As Long

For i = 2 To 1000

If Cells(i, 6) < Date Then
Range(Cells(i, 3), Cells(i, 6)).Clear
Else
End If

Next i
End Sub

    Maybe with the Worksheet_Activate event.

     

    After opening the attached file the sheet "Tabelle1" is shown with all the data. If you select "Tabelle3" and then again activate "Tabelle1" the Worksheet_Activate event is executed.

4 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Sep 13, 2022

    heathermarie923 

    Sub clear_C_to_F()

Dim i As Long

For i = 2 To 1000

If Cells(i, 6) < Date Then
Range(Cells(i, 3), Cells(i, 6)).Clear
Else
End If

Next i

End Sub

    Maybe with these lines of code. In the attached file you can click the button in cell H2 to run the macro.

    • heathermarie923's avatar
      heathermarie923
      Copper Contributor
      Sep 13, 2022
      This is great! Is there anyway to automate the process when the spreadsheet is opened?
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Sep 13, 2022

        heathermarie923 

        Without clicking the button?

        Private Sub Worksheet_Activate()
Dim i As Long

For i = 2 To 1000

If Cells(i, 6) < Date Then
Range(Cells(i, 3), Cells(i, 6)).Clear
Else
End If

Next i
End Sub

        Maybe with the Worksheet_Activate event.

         

        After opening the attached file the sheet "Tabelle1" is shown with all the data. If you select "Tabelle3" and then again activate "Tabelle1" the Worksheet_Activate event is executed.

Resources