• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Eloise T

Active Member
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 <<<
Code:
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!
 

Attachments

Last edited by a moderator:

Marc L

Excel Ninja
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

New Member
;)
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

Excel Ninja
Merci ! But 'mec' is somewhat too familiar …​
Why ? Maybe 'cause the shorter answer, the less confusion.​
 

Eloise T

Active Member
When trying to use your example as a template:

>>> use code - tags <<<
Code:
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
 
Last edited by a moderator:

Marc L

Excel Ninja
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 …​
 
Top