Clear Data without VBA

GMC The Macro Man

Board Regular
Joined
Mar 23, 2023
Messages
100
Office Version
  1. 2021
Platform
  1. Windows
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
ABCDEFGHIJK
1DATE01/09/2023DATE01/05/2024
2ID NumberNameClassDOBTeacherCARSRAWSAttendanceYear Group
3id1234Jayden DuffyP130/05/2018Mrs Smith5y 3m1.55y 11ma85%S1
4id1235Kylah ScottP114/02/2018Mrs Smith5y 6m1.56y 2mx84%S1
5id1236Lucia HartP206/02/2017Mr Brown6y 6m2.87y 2md69%S1
6id1237Karthik HarisonP204/02/2017Mr Brown6y 6m2.17y 2md75%S1
7id1238Carson rundleP324/06/2016Mr Jones7y 2m3.27y 10me95%S2
8id1239Scott WilkinsonP309/02/2016Mr Jones7y 6m3.58y 2mw98%S2
9id1240Georgia TervitP408/02/2015Miss Green8y 6m4.19y 2ms88%S2
10id1241Tilly BeadieP422/01/2015Miss Green8y 7m4.69y 3mt74%S2
11id1242Isaac KhanP503/02/2014Mrs White9y 6m4.810y 2mh68%S3
12id1243Sebastian PopaP502/02/2014Mrs White9y 6m5.210y 2my88%S3
13id1244Ivy McGheeP601/02/2013Mr Black10y 6m5.211y 2my89%S3
14id1245Macy BryceP605/02/2013Mr Black10y 6m5.211y 2mk95%S3
Sheet1
Cell Formulas
RangeFormula
F3:F14F3=IF(ISBLANK($D2),"",IFERROR(DATEDIF(0,($F$1-$D3),"Y")&"y "&DATEDIF(0,($F$1-$D3),"YM")&"m",""))
H3:H14H3=IF(ISBLANK($D2),"",IFERROR(DATEDIF(0,($H$1-$D3),"Y")&"y "&DATEDIF(0,($H$1-$D3),"YM")&"m",""))
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
"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.
 
Upvote 1
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.
 
Upvote 1
"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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 1
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:
Rich (BB code):
Sub Cleardata()
    Application.ScreenUpdating = False
    With ActiveSheet
        .Unprotect "xxxx"
        .UsedRange.Offset(2).SpecialCells(xlConstants).ClearContents
        .Protect "xxxx"
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 1
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?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top