Macro to clear range contents when cell contents are changed by us
 

Hi.

Virtually no VB skills and really need a solution.

I have a worksheet with 15 rows of information that are entered by a user
via data validated drop down menus in ea cell of the row. When the user
changes the selection in the first cell [D18,D19,D20...D34] I need to clear
the contents in the range [L18:T18,L19:T19...L34:T34] so that I can safely
eliminate any remnant data from causing problems in the rest of the workbook.

Input into the first cell (above) is via a data validated (dynamic range)
list (if that matters).

If it matters, I will be protecting the worksheets and workbook...

Help???

Macro to clear range contents when cell contents are changed by us
 

give this a try and see if it does what you want.

Put this code in the worksheet module of the sheet you are trying to
use.
If you have any questions let me know.

--------------------------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rchange As Integer
Rchange = Target.Row ' row number selected
If Rchange 17 And Rchange < 35 Then ' make sure only applies to
rows 18 to 34
If Target.Address = "$D" & "$" & Rchange Then
'MsgBox "Target address changed :" & Target.Address
Range("L" & Rchange, "T" & Rchange).Clear ' clears cells
for in this row for cols L to T
MsgBox "Range: " & " L" & Rchange & " to " & " T" &
Rchange & " Cleared" ' optional
End If
Else
End If
End Sub

Macro to clear range contents when cell contents are changed b
 

Steve;

Works just like I'd hoped!

Next question: this clears the formatting too (I have the borders formatted
so that they print... any way to leave the formatting alone and just clear
the range contents?

Thanks!

"stevebriz" wrote:


give this a try and see if it does what you want.

Put this code in the worksheet module of the sheet you are trying to
use.
If you have any questions let me know.

--------------------------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rchange As Integer
Rchange = Target.Row ' row number selected
If Rchange 17 And Rchange < 35 Then ' make sure only applies to
rows 18 to 34
If Target.Address = "$D" & "$" & Rchange Then
'MsgBox "Target address changed :" & Target.Address
Range("L" & Rchange, "T" & Rchange).Clear ' clears cells
for in this row for cols L to T
MsgBox "Range: " & " L" & Rchange & " to " & " T" &
Rchange & " Cleared" ' optional
End If
Else
End If
End Sub

Macro to clear range contents when cell contents are changed b
 

try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rchange As Integer
Rchange = Target.Row ' row number selected
If Rchange 17 And Rchange < 35 Then ' make sure only applies to
rows 18 to 34
If Target.Address = "$D" & "$" & Rchange Then
'MsgBox "Target address changed :" & Target.Address
Range("L" & Rchange, "T" & Rchange).Value = vbNullString '
clears cells for in this row for cols L to T
MsgBox "Range: " & " L" & Rchange & " to " & " T" &
Rchange & " Cleared" ' optional
End If
Else
End If
End Sub

Macro to clear range contents when cell contents are changed b
 

THANKS!

I tried:
Range("L" & Rchange, "T" & Rchange).ClearContents

and it seemed to work... does this pose any problems so that I should use the

Range("L" & Rchange, "T" & Rchange).Value = vbNullString

that you suggested instead?

This stuff is great... I really need to learn VB I think. Any suggestions
on how to get edumacated?

Thanks!
"stevebriz" wrote:

try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rchange As Integer
Rchange = Target.Row ' row number selected
If Rchange 17 And Rchange < 35 Then ' make sure only applies to
rows 18 to 34
If Target.Address = "$D" & "$" & Rchange Then
'MsgBox "Target address changed :" & Target.Address
Range("L" & Rchange, "T" & Rchange).Value = vbNullString '
clears cells for in this row for cols L to T
MsgBox "Range: " & " L" & Rchange & " to " & " T" &
Rchange & " Cleared" ' optional
End If
Else
End If
End Sub

Macro to clear range contents when cell contents are changed b
 

clear.contents is fine...Clears the formulas from the range. Clears the
data from a chart but leaves the formatting.

A good starting point is Excel VBA programming for dummies...this how I
started off..then tried a few different things and search the net if I
got stuck..and use the forums when really stuck!

Macro to clear range contents when cell contents are changed b
 

So if I had formulas in those cells I'd want to use the:

Range("L" & Rchange, "T" & Rchange).Value = vbNullString

code instead, right? Which is probably smarter UNLESS I actually wanted to
clear out the formulas (which doesn't seem to likely).

Thanks for all the help!

"stevebriz" wrote:


clear.contents is fine...Clears the formulas from the range. Clears the
data from a chart but leaves the formatting.

A good starting point is Excel VBA programming for dummies...this how I
started off..then tried a few different things and search the net if I
got stuck..and use the forums when really stuck!

Macro to clear range contents when cell contents are changed b
 

Steve E wrote:
So if I had formulas in those cells I'd want to use the:

Range("L" & Rchange, "T" & Rchange).Value = vbNullString

Nope....VbNullString cleans out formulas aswell.
This I don't see as problem as...I would never set the range to to
delete formulas I wanted to keep.

Macro to clear range contents when cell contents are changed b
 

This is very close to something I want to do, but I just can figure out how
to make changes to this code to get it to work.

Here's what I'm looking for:

When all the cells in range A5:E5 = zero
then
delete the value in F5:N5

And I need to repeat this down from rows 5 through 50.

THANKS!

Macro to clear range contents when cell contents are changed b
 

Sub Test()
Dim j As Integer
For j = 5 To 50
If Application.WorksheetFunction.Sum(Range("a" & CStr(j) & ":e" &
CStr(j))) = 0 Then _
Range(("f") & CStr(j) & ":n" & CStr(j)).Clear
Next j
End Sub
James
"Shelly" wrote in message
...
This is very close to something I want to do, but I just can figure out
how
to make changes to this code to get it to work.

Here's what I'm looking for:

When all the cells in range A5:E5 = zero
then
delete the value in F5:N5

And I need to repeat this down from rows 5 through 50.

THANKS!

Macro to clear range contents when cell contents are changed b
 

Thanks, James. But now I realize that the value of the cells isn't "0",
because there is a formula in each of these cells.

How can I do this so it will clear the F5:N5 range if A5:E5 contains no
value, but does contain formulas?

Macro to clear range contents when cell contents are changed b
 

Shelly, I cannot duplicate the problem. James
"Shelly" wrote in message
...
Thanks, James. But now I realize that the value of the cells isn't "0",
because there is a formula in each of these cells.

How can I do this so it will clear the F5:N5 range if A5:E5 contains no
value, but does contain formulas?

Macro to clear range contents when cell contents are changed b
 

Shelly, I replied earlier, but some reason my reply didn't show up. I
cannot see any problem with my code, regardless of whether there are
formulas or not. James

"Shelly" wrote in message
...
Thanks, James. But now I realize that the value of the cells isn't "0",
because there is a formula in each of these cells.

How can I do this so it will clear the F5:N5 range if A5:E5 contains no
value, but does contain formulas?