How to create a macro that clears entries on a worksheet?

[Eloise T]

I need a macro that clears the alphanumerical entries of a worksheet so the user doesn't have to do it manually.
Please see the yellow highlighted cells (A-4..A28, A30..A36, A38..A47, A50..E76, F51..F76, and H75) on the attached worksheet.
Is there a better way than listing each cell...see below?

>>> use code - tags <<<

Sub clear_cells()
Range("'calculator'!a4").ClearContents
Range("'calculator'!a5").ClearContents
Range("'calculator'!a6").ClearContents
Range("'calculator'!a7").ClearContents
Range("'calculator'!a8").ClearContents
Range("'calculator'!a9").ClearContents
Range("'calculator'!a10").ClearContents
. . . . .
End sub

Thank you in advance!

 

[Marc L]

The best way is to name the range like for example '_Entries' and to use this named range in the VBA procedure : [_Entries].ClearContents …​

​

Or with the classic range way : [A4:A47,A50:E50,A51:F76,H75].ClearContents …​

​

 

[IDidNotDoIt]

The best way is to name the range like for example '_Entries' and to use this named range in the VBA procedure : [_Entries].ClearContents …​

​

Or with the classic range way : [A4:A47,A50:E50,A51:F76,H75].ClearContents …​

​

I've been always wondering why this guy's (should I say mec?, devrais-je dire) answers are short, clear, concise and effective...

 

[Marc L]

Merci ! But 'mec' is somewhat too familiar …​

​

Why ? Maybe 'cause the shorter answer, the less confusion.​

​

 

[Eloise T]

When trying to use your example as a template:

>>> use code - tags <<<

Range("'calculator'![a30:a36]").ClearContents
' Range("'calculator'!a30").ClearContents
' Range("'calculator'!a31").ClearContents
' Range("'calculator'!a32").ClearContents
' Range("'calculator'!a33").ClearContents
' Range("'calculator'!a34").ClearContents
' Range("'calculator'!a35").ClearContents
' Range("'calculator'!a36").ClearContents

I got:
Run-time error '1004'

Method 'Range' of object'_Global' failed

What am I missing?
... code - tags
... Your tested file

 

[Marc L]

Bad syntax obviously : just remove the brackets in red within your Range statement :​

​

Range("'calculator'![a30:a36]").ClearContents​

​

As you should have seen in my post #2 the Evaluate (a VBA help must read !) short syntax is [calculator!A30:A36].ClearContents …​

​

 

[Debaser]

Or use:

Sheets("calculator").Range("A4:A28,A30:A36,A38:A47,A50:E76,F51:F76,H75").ClearContents

 

[Eloise T]

Or use:

Sheets("calculator").Range("A4:A28,A30:A36,A38:A47,A50:E76,F51:F76,H75").ClearContents

Thank you, Debaser!