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

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

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

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

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

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!