Macro to Clear Cell Contents based on Cell Value in another Sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CH - 26 Jan 2007 17:20 GMT

Sorry for the Newbie question but.......

How do I create a macro to clear the contents of cells in one worksheet
based on the value of a cell in another worksheet?

For example: on SHEET 1 the value of cell A1 can be Yes or No. If A1 =
"Yes" then I want to clear the values in cell range A1:C5 of SHEET 2,
if "No" then don't clear the values.

Thanks in advance.

Reply to this Message

Dave Peterson - 26 Jan 2007 17:54 GMT

Option Explicit
sub testme()
dim rng1 as range
dim rng2 as range

set rng1 = worksheets("sheet1").range("a1")
set rng2 = worksheets("sheet2").range("a1:c5")

if lcase(rng1.value) = "yes" then
rng2.clearcontents
end if
end sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

> Sorry for the Newbie question but.......
>
[quoted text clipped - 6 lines]
>
> Thanks in advance.

Signature

Dave Peterson

Reply to this Message

CH - 26 Jan 2007 19:31 GMT

Thanks that worked.

Is there a way to "undo" the clear when toggling back-and-forth between
"Yes" and "No". For example if the cell = "Yes" then clear the cells,
if cell = "No" then place original values back into the cell?

Thanks for the website too.

Reply to this Message

Dave Peterson - 26 Jan 2007 20:37 GMT

If you store those values somewhere, you could put them back.

But I would guess that it's kind of scary. If the user changes any of those
cells, you may be overwriting stuff that they want with older values.

> Thanks that worked.
>
[quoted text clipped - 3 lines]
>
> Thanks for the website too.

Signature

Dave Peterson

Reply to this Message

Gord Dibben - 26 Jan 2007 18:01 GMT

Option Compare Text
Sub clear_stuff()
If Sheets("Sheet1").Range("A1").Value = "Yes" Then
Sheets("Sheet2").Range("A1:C5").ClearContents
End If
End Sub

Gord Dibben MS Excel MVP

>Sorry for the Newbie question but.......
>
[quoted text clipped - 6 lines]
>
>Thanks in advance.

Reply to this Message

Rate this thread: