Macro to clear contents of cells by a column header date.

[Kepowe]

I have a planning form I am using and want a macro that I can just push the button and auto clear content of cell ranges across the sheet. I already have an auto hide columns macro working. Now I need one that will clear contents for a range of rows up to the header with the date of yesterday. I need to keep the rows under the desired range as they have formulas. Row 1 has dates that spread through the end of the year. I will just say that rows C2:TH36 are the cells that need cleared daily or potentially weekly with the macro. But need it to only clear the rows and column data up to yesterday's date. I need to always keep A and B column data and anything from today's date and forward. Below the above mentioned cell range I have formula's and I do not want to have those cells contents deleted. Below is a snip of the columns. Row 1 is the dates and row 3 through 11 up to column AB needs cleared.

 

[JLGWhiz]

Test thison a copy of your file first. This assumes an Active-X button and the code would go in the respective sheet code module. I used 'cmdClear' as the button name, you will need to edit the code to substitute the actual button name.

Private Sub cmdClear_Click()  'Change button name
Dim fn As Range
Set fn = Rows(1).Find(Format(Date, "m/d"), , xlValues, xlPart)
    If Not fn Is Nothing Then
        Range("C2", Cells(36, fn.Column - 1)).ClearContents
    End If
End Sub

 

[Kepowe]

Test thison a copy of your file first. This assumes an Active-X button and the code would go in the respective sheet code module. I used 'cmdClear' as the button name, you will need to edit the code to substitute the actual button name.

Private Sub cmdClear_Click()  'Change button name
Dim fn As Range
Set fn = Rows(1).Find(Format(Date, "m/d"), , xlValues, xlPart)
    If Not fn Is Nothing Then
        Range("C2", Cells(36, fn.Column - 1)).ClearContents
    End If
End Sub

Whiz,
Thank you so much for the code. It does work if I run it in the design mode but having a hard time assigning it to a active-x button.

 

[Kepowe]

Whiz,
Thank you so much for the code. It does work if I run it in the design mode but having a hard time assigning it to a active-x button.

Suppose I should have mentioned I have 6 sheets that I need the same macro but different cell ranges for the clearing data.

 

[JLGWhiz]

Whiz,
Thank you so much for the code. It does work if I run it in the design mode but having a hard time assigning it to a active-x button.

To use the code with an Active-X button.
1. Go to Developer on the Ribbon, then click 'Insert'
2. Click the comman button in the Active-X Tool Box
3. Move the cross hair pointer to where you want the button to appear on the sheet, then left click to display the button
4. While in design mode, right click the button, then click 'View Code' in the pop up menu
5. Copy the code, excluding the Title and End Sub lines to the code window that appears between the Title and End Sub that Exce automatically formed.
6. If the properies window is already showing for the button, then on the Name line, put the name for the button, press enter
7. Note that the button macro now uses that name for the Click event in the title line of the code..

If you cannot get it to work with the Active-X button, then
1. Delete the code from the worksheet code module
2. Change the Title line to remove the '_Click'
3. Copy the code to a standard code module (module1)
4. Go to Developer on the ribbon and click 'Insert'
5. Click the button in the Form Controls Tool Box.
6. Move the cross hair pointer to where you want the button to appear and left click to display the button.
7. Select 'Assign Macro' from the pop up menu that should appear when you display the button
8. Find the macro name in the list macros in the dialog box that appears, and click on it once, then click OK.
9. The button should now be attached to your macro.

Active-X code goes into the control's parent code module as Click events, while the code for Form Controls controls goes into a public code module similar to called macros.

 

[Kepowe]

To use the code with an Active-X button.
1. Go to Developer on the Ribbon, then click 'Insert'
2. Click the comman button in the Active-X Tool Box
3. Move the cross hair pointer to where you want the button to appear on the sheet, then left click to display the button
4. While in design mode, right click the button, then click 'View Code' in the pop up menu
5. Copy the code, excluding the Title and End Sub lines to the code window that appears between the Title and End Sub that Exce automatically formed.
6. If the properies window is already showing for the button, then on the Name line, put the name for the button, press enter
7. Note that the button macro now uses that name for the Click event in the title line of the code..

If you cannot get it to work with the Active-X button, then
1. Delete the code from the worksheet code module
2. Change the Title line to remove the '_Click'
3. Copy the code to a standard code module (module1)
4. Go to Developer on the ribbon and click 'Insert'
5. Click the button in the Form Controls Tool Box.
6. Move the cross hair pointer to where you want the button to appear and left click to display the button.
7. Select 'Assign Macro' from the pop up menu that should appear when you display the button
8. Find the macro name in the list macros in the dialog box that appears, and click on it once, then click OK.
9. The button should now be attached to your macro.

Active-X code goes into the control's parent code module as Click events, while the code for Form Controls controls goes into a public code module similar to called macros.

Ok, got it to work as a macro. Thanks so much for your time.

 

[JLGWhiz]

You're welcome,]
regards, JLG