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

Kepowe

New Member
Joined
Aug 19, 2020
Messages
4
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.

1597861658883.png
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,546
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Aug 19, 2020
Messages
4
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.
VBA Code:
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

New Member
Joined
Aug 19, 2020
Messages
4
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,546
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

Kepowe

New Member
Joined
Aug 19, 2020
Messages
4
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,546
Office Version
  1. 2013
Platform
  1. Windows
You're welcome,]
regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,114,560
Messages
5,548,737
Members
410,869
Latest member
eSoftToolsNSFtoPSTConvert
Top