MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Clearing contents


Posted by Greg on February 28, 2001 7:03 AM

A few posts back I read this and is of great use to me
As you can see I would like to refer this clearing
to a range of cells...
I tried this

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("$B$3:$H$5") = "." Then Range("$B$3:$H$5").ClearContents
End Sub

It came up with a run time error....


so now I am writing in this below and well there has to be an
easier way
help please


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B3") = "A" Then Range("B3").ClearContents
If Range("C3") = "A" Then Range("C3").ClearContents
If Range("D3") = "A" Then Range("D3").ClearContents
If Range("E3") = "A" Then Range("E3").ClearContents
If Range("F3") = "A" Then Range("F3").ClearContents
If Range("G3") = "A" Then Range("G3").ClearContents
If Range("H3") = "A" Then Range("H3").ClearContents
If Range("B4") = "A" Then Range("B4").ClearContents
If Range("C4") = "A" Then Range("C4").ClearContents
If Range("D4") = "A" Then Range("D4").ClearContents
If Range("E4") = "A" Then Range("E4").ClearContents
If Range("F4") = "A" Then Range("F4").ClearContents
If Range("G4") = "A" Then Range("G4").ClearContents
If Range("H4") = "A" Then Range("H4").ClearContents
If Range("B5") = "A" Then Range("B5").ClearContents
If Range("C5") = "A" Then Range("C5").ClearContents
If Range("D5") = "A" Then Range("D5").ClearContents
If Range("E5") = "A" Then Range("E5").ClearContents
If Range("F5") = "A" Then Range("F5").ClearContents
If Range("G5") = "A" Then Range("G5").ClearContents
If Range("H5") = "A" Then Range("H5").ClearContents
End Sub


Posted by AB on February 28, 2001 7:57 AM

Hi Greg,

It would look something like...

For Each cell In Range("B3:H5")
If cell.Text = "A" Then cell.ClearContents
Next cell

Private Sub Worksheet_Change(ByVal Target As Range) If Range("B3") = "A" Then Range("B3").ClearContents If Range("C3") = "A" Then Range("C3").ClearContents If Range("D3") = "A" Then Range("D3").ClearContents If Range("E3") = "A" Then Range("E3").ClearContents If Range("F3") = "A" Then Range("F3").ClearContents If Range("G3") = "A" Then Range("G3").ClearContents If Range("H3") = "A" Then Range("H3").ClearContents If Range("B4") = "A" Then Range("B4").ClearContents If Range("C4") = "A" Then Range("C4").ClearContents If Range("D4") = "A" Then Range("D4").ClearContents If Range("E4") = "A" Then Range("E4").ClearContents If Range("F4") = "A" Then Range("F4").ClearContents If Range("G4") = "A" Then Range("G4").ClearContents If Range("H4") = "A" Then Range("H4").ClearContents If Range("B5") = "A" Then Range("B5").ClearContents If Range("C5") = "A" Then Range("C5").ClearContents If Range("D5") = "A" Then Range("D5").ClearContents If Range("E5") = "A" Then Range("E5").ClearContents If Range("F5") = "A" Then Range("F5").ClearContents If Range("G5") = "A" Then Range("G5").ClearContents If Range("H5") = "A" Then Range("H5").ClearContents End Sub

The Excel Logic Page