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?


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391513

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


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=391513

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
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Lewis Koh" wrote
in message ...

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?


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile:
http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391513

wow, that's a nice function :) Just one click to delete the selected
cells. Thanks!!
By the way, how do I use the function protect cell? I protected the
cell but I still can delete its content. 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.


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391513

Max Wrote:

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?


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391513

... 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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Lewis Koh" wrote
in message ...

wow, that's a nice function :) Just one click to delete the selected
cells. Thanks!!
By the way, how do I use the function protect cell? I protected the
cell but I still can delete its content. 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.


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile:
http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391513

"Lewis Koh" wrote:
.. 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".

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
could pack in a bit more before we hit the limit <g

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

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Minor typo correction:
Select MyRange (which should appear under the "Names ....

should read as:
Select MyRange (which should then appear under the "Names ...

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Hi 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?

[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 checked
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 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 specifif cells?


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391513

"Lewis Koh" wrote

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.

well, I tried that earlier and it didn't work for me, too <g,
hence the earlier response to the effect ..

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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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


--
Lewis Koh
------------------------------------------------------------------------
Lewis Koh's Profile: http://www.excelforum.com/member.php...o&userid=25712
View this thread: http://www.excelforum.com/showthread...hreadid=391513

You're welcome, Lewis !

... Excel can be so fun ...
and that's one reason why this respondent hangs around .. <g
cheers
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Lewis Koh" wrote in
message ...

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