Clear Data without VBA

[GMC The Macro Man]

Hi team, a Happy New Year to you all (is it still ok to say that ??? )
I have created a report for School Teachers who enter Data on to the main page and then the report goes away and does all it's calculations and displays the output on different worksheets.
On the main page there are about 6 columns that contain formulas that I don't want the teachers to over-write so I have locked and protected these particular cells in the sheet
However, if the teachers want to delete ALL the data on the main page, they need to select individual areas and not the protected columns - it's a bit messy as they can do this numerous times.

Is there an easy way for the teachers to delete ALL the information without removing the Formulas on the main page?
I know I can create a Button in VBA and write a script to Clear the Data, but that would mean saving the file in XLSM. Having a pop up when saving the file to say "are you sure you want save as a XLSM / macro" would throw them. Trust me
I have also tried the F5 - Go to Special, (Constants) but you cannot clear the data if the sheet is protected and I don't want to leave the columns unprotected and this data cannot be saved somewhere else. The output has to show on the main page.

I have attached a wee excerpt of the sheet which shows some basic details of what would be entered (This could be 40 columns wide by 400 rows.)
The grey columns ("F" & "H") are the ones in the formula and calculates the age of the pupil form their DOB to the date in row1

So basically, I want and easy way for the teacher who is a novice in excel to "hit a button" that deletes all the data but not the grey columns, but i still need the grey columns to be locked so they don't type over them.

Thanks in Advance
GMC

Book1
	A	B	C	D	E	F	G	H	I	J	K
1					DATE	01/09/2023	DATE	01/05/2024
2	ID Number	Name	Class	DOB	Teacher	CA	RS	RA	WS	Attendance	Year Group
3	id1234	Jayden Duffy	P1	30/05/2018	Mrs Smith	5y 3m	1.5	5y 11m	a	85%	S1
4	id1235	Kylah Scott	P1	14/02/2018	Mrs Smith	5y 6m	1.5	6y 2m	x	84%	S1
5	id1236	Lucia Hart	P2	06/02/2017	Mr Brown	6y 6m	2.8	7y 2m	d	69%	S1
6	id1237	Karthik Harison	P2	04/02/2017	Mr Brown	6y 6m	2.1	7y 2m	d	75%	S1
7	id1238	Carson rundle	P3	24/06/2016	Mr Jones	7y 2m	3.2	7y 10m	e	95%	S2
8	id1239	Scott Wilkinson	P3	09/02/2016	Mr Jones	7y 6m	3.5	8y 2m	w	98%	S2
9	id1240	Georgia Tervit	P4	08/02/2015	Miss Green	8y 6m	4.1	9y 2m	s	88%	S2
10	id1241	Tilly Beadie	P4	22/01/2015	Miss Green	8y 7m	4.6	9y 3m	t	74%	S2
11	id1242	Isaac Khan	P5	03/02/2014	Mrs White	9y 6m	4.8	10y 2m	h	68%	S3
12	id1243	Sebastian Popa	P5	02/02/2014	Mrs White	9y 6m	5.2	10y 2m	y	88%	S3
13	id1244	Ivy McGhee	P6	01/02/2013	Mr Black	10y 6m	5.2	11y 2m	y	89%	S3
14	id1245	Macy Bryce	P6	05/02/2013	Mr Black	10y 6m	5.2	11y 2m	k	95%	S3
Sheet1

Cell Formulas
Range	Formula
F3:F14	F3	=IF(ISBLANK($D2),"",IFERROR(DATEDIF(0,($F$1-$D3),"Y")&"y "&DATEDIF(0,($F$1-$D3),"YM")&"m",""))
H3:H14	H3	=IF(ISBLANK($D2),"",IFERROR(DATEDIF(0,($H$1-$D3),"Y")&"y "&DATEDIF(0,($H$1-$D3),"YM")&"m",""))

 

[Joe4]

"Hit a button" = VBA code

What you seem to be asking is "can I have VBA-type code functionality without using VBA". I don't think that is possible.
You can either have formulas in cells or hard-coded (or blank) values, but never both at the same time.
To go from one to the other will either require manual work or VBA.

Maybe come about this from a different angle.
Why would the teachers need to delete all the data?
Is it that they are creating a new report with new data each month?

If so, then maybe a better option would be to create an Excel template, that contains all the headers, formulas, structure, but no data.
And then each month (or however how often they need to create a new report), they just start from this blank template and populate the data.

 

[jkpieterse]

If they are in an unprotected cell they can press control+a to select all surrounding data and hit the del key. This is provided you have not allowed to select protected cells in the protection settings. This isn't great, but it limits the amount of work to clear the editable cells. But, if you provide them with a macro *you* are the one who has to save as xlsm, when they open the file they only have to enable macro's once.

 

[GMC The Macro Man]

"Hit a button" = VBA code

What you seem to be asking is "can I have VBA-type code functionality without using VBA". I don't think that is possible.
You can either have formulas in cells or hard-coded (or blank) values, but never both at the same time.
To go from one to the other will either require manual work or VBA.

Maybe come about this from a different angle.
Why would the teachers need to delete all the data?
Is it that they are creating a new report with new data each month?

If so, then maybe a better option would be to create an Excel template, that contains all the headers, formulas, structure, but no data.
And then each month (or however how often they need to create a new report), they just start from this blank template and populate the data.

Thank you Joe, yeah it's a bit messy I know.

 

[GMC The Macro Man]

If they are in an unprotected cell they can press control+a to select all surrounding data and hit the del key. This is provided you have not allowed to select protected cells in the protection settings. This isn't great, but it limits the amount of work to clear the editable cells. But, if you provide them with a macro *you* are the one who has to save as xlsm, when they open the file they only have to enable macro's once.

Thank you Jan, I will try both your suggestions here to see if I can get a resolve. I will let you know if I find a good solution here

 

[jkpieterse]

Hmm, "Hit a button" can also be Office script (which isn't stored with the workbook). Do you have the Automate tab visible in Excel?

 

[GMC The Macro Man]

Hmm, "Hit a button" can also be Office script (which isn't stored with the workbook). Do you have the Automate tab visible in Excel?

Yes I do

 

[jkpieterse]

A quick and dirty solution: name the editable cells. Then the users can select that name in the name box (to the left of the formula bar) and hit the del key to clear everything.

 

[mumps]

First of all, I would change the "$D2" in your formulas to "$D3".
In this macro change the password (in red) to one of your choosing:

Sub Cleardata()
    Application.ScreenUpdating = False
    With ActiveSheet
        .Unprotect "xxxx"
        .UsedRange.Offset(2).SpecialCells(xlConstants).ClearContents
        .Protect "xxxx"
    End With
    Application.ScreenUpdating = True
End Sub

 

[GMC The Macro Man]

A quick and dirty solution: name the editable cells. Then the users can select that name in the name box (to the left of the formula bar) and hit the del key to clear everything.

Thanks Jan. I work on Office 2021 and the school works on Office 365. If I run this command would be compatible at their end?