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",""))

 

[GMC The Macro Man]

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

Thanks MUMPS, and apologies about $D2, my error. If i decide to go down the VBA route I will certainly use this script

 

[jkpieterse]

Yes. Here are your steps to prepare:
- Select the first area of editable cells
- Hold down the control key and select the other editable areas
- Click in the name box and type the name
- Press Enter

Now experiment: select any cell, click in the name box and choose the newly added name. If it selects all the cells you expect it to, press the Del key.

 

[mumps]

You are very welcome. As @Joe4 has said, I don't think that you can do what you want without using VBA. I've modified the code to ask for confirmation before deleting the data.

Sub Cleardata()
    Application.ScreenUpdating = False
    If MsgBox("Are your sure you want to delete all the data?", vbYesNo) = vbYes Then
        With ActiveSheet
            .Unprotect "xxxx"
            .UsedRange.Offset(2).SpecialCells(xlConstants).ClearContents
            .Protect "xxxx"
        End With
    End If
    Application.ScreenUpdating = True
End Sub

You can simply insert a button named "Delete Data" on the sheet and assign the macro to it.