Clearing multiple cells in 1 click?

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?

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

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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.

[QUOTE=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>)
--
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?

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

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" 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 <at>yahoo<dot>com
----

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

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

> ... 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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote
in message news:[email protected]...
>
> 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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----

"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 <at>yahoo<dot>com
----

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 <at>yahoo<dot>com
----
"Lewis Koh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Max Hmm.....Excel can be so fun *sigh* so many unknown
> functions, I wished I knew them all.