Clearing multiple cells in 1 click?

[Lewis Koh]

A B
1 Liquor $100.00
2 Medical $50.00
3 breakfast $2.50
4 lunch $1.20
5 dinner $5.30
6 *Total:* =SUM(B1:B5)
7 breakfast $13.50
8 water $1.10
9 Dinner $80.00
10 others $13.90
11 *Total:* =SUM(B7:B10)

Is it possible to create a button to clear only A1:B5 and A7:B10
Something like
if A12=1, A1:B5 and A7:B10 will be cleared

 

[dominicb]

Good morning Lewis Koh

This code will do the trick.

Sub Mod1()
If Range("A12").Value = 1 Then
Range("A1:B5,A7:B11").Clear
End If
End Sub

Copy it into a blank module, and draw yourself a button using the Forms
toolbar, when you've created your button right click it, and select
assign macro, and select Mod1 (or whatever you've chosen to call it).

HTH

DominicB

 

[Max]

Think you'd need a sub to do that. Formulas cannot clear cells.

But here's something simple to play with
(does the job with just 2 "clicks")

Select A1:B5, hold down CTRL then select A7:B10
Click inside the namebox*, type a name, say: MyRange
Press ENTER
*the box with the the droparrow just to the left of the "=" sign

Now whenever you want to clear A1:B5 and A7:B10,
just select "MyRange" from the namebox droplist and press Delete
(Just an extra "click" ! <g>)

 

[Lewis Koh]

wow, that's a nice function Just one click to delete the selecte
cells. Thanks!!
By the way, how do I use the function protect cell? I protected th
cell but I still can delete its content. I was wondering if I coul
protect whatever cells that I do not one to delete so that I coul
select the whole worksheet to delete the rest

 

[Lewis Koh]

Think you'd need a sub to do that. Formulas cannot clear cells.

But here's something simple to play with
(does the job with just 2 "clicks")

Select A1:B5, hold down CTRL then select A7:B10
Click inside the namebox*, type a name, say: MyRange
Press ENTER
*the box with the the droparrow just to the left of the "=" sign

Now whenever you want to clear A1:B5 and A7:B10,
just select "MyRange" from the namebox droplist and press Delete
(Just an extra "click" ! <g>)
--
Rgds
Max
xl 97
QUOTE]

Hi Max, is there a limitation as to how many selectations I can select?
I tried to select 31 "range" and save it as ""MyRange" but once I
pressed enter, only
19 selectations are selected. The other 12 ranges had to save it as
"MyRange2".

How do I clear the "MyRange" if I wanted to use "MyRange" for other
selections?

 

[Max]

... I protected the cell but I still can delete its content

You need to apply the protection via:
Tools > Protection > Protect Sheet > Passwrd? > OK

.. I was wondering if I could protect whatever cells
that I do not one to delete so that I could
select the whole worksheet to delete the rest.

Think it won't work in this way. But we could try it the other way round
where we select and unlock only the input cell range(s), then apply the
sheet protection as above.

Assuming MyRange covers the 2 input tanges

Select the entire sheet > Format > Cells > Protection tab
Ensure "Locked" is checked > OK
(This step is usually not necessary as by default all cells are checked as:
Locked.)

Select MyRange > Format > Cells > Protection tab
Uncheck "Locked" > OK

Now click Tools > Protect > Protect Sheet ...

The entire sheet will be protected except MyRange

 

[Max]

Think named/defined ranges are actually formulas, and are hence subject to
the max formula length limit (1024 chars?). Believe this limit is what hit
you. If we were to shorten the sheetname to a single digit or alpha (e.g.:
use: "1" or "A" instead of the longish:"Sheet1", "SheetA", then I think we

Click Insert > Name > Define
(That's where the options to delete, add, amend named/defined ranges lie)

Select MyRange (which should appear under the "Names in workbook") box,
then amend the range(s) appearing under the "Refers to:" box

 

[Max]

Minor typo correction:

Select MyRange (which should appear under the "Names ....

should read as:

 

[Lewis Koh]

Hi Max,

Shorten the sheetname can allow me to select 31 "range" too. But
could select 20 ranges now. I assume defining 2 "Ranges" is the onl
way to do a quick clear of the selected cells?

[qoute]
Select the entire sheet > Format > Cells > Protection tab
Ensure "Locked" is checked > OK
(This step is usually not necessary as by default all cells are checke
as:
Locked.)

Select MyRange > Format > Cells > Protection tab
Uncheck "Locked" > OK

Now click Tools > Protect > Protect Sheet ...
[qoute]

Actually I wanted to protect certain cells so that I could highligh
the whole worksheet and delete those unprotected cells. But Once I tr
that, it will still prompt that the cell selected are protected. Ho
can I make it simplier to delete specifif cells

 

[Max]

Shorten the sheetname can allow me to select 31 "range" too. But I
could select 20 ranges now. I assume defining 2 "Ranges" is the only
way to do a quick clear of the selected cells?

I'd guess so. Hang around awhile.
Maybe others would step in here and offer you better insights.

Actually I wanted to protect certain cells so that I could highlight
the whole worksheet and delete those unprotected cells. But Once I try
that, it will still prompt that the cell selected are protected.

How can I make it simplier to delete specific cells?

I've given you all I've got <g>.
Hang around awhile. Maybe others would step in here
and offer you some other insights.

 

[Lewis Koh]

Thanks Max Hmm.....Excel can be so fun *sigh* so many unknow
functions, I wished I knew them all

 

[Max]

You're welcome, Lewis !

... Excel can be so fun ...

and that's one reason why this respondent hangs around .. <g>
cheers