Clear Active Cell by Offset

[ramnewbietoaccess]

In Excel 2000 I have rows whereby columns a and b are populated by a selection from a listbox in a user form. Columns c:n are then populated manually. I would like the entire row to clear if the contents from column a are deleted just for user friendliness. I am using the following code.

My question is this. The code works great SOMETIMES and other times it doesnt work at all. Is there a reason this would be unstabile?

And also, is there a better suggestion or an improved suggestion to accomplish this?

Thank you in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column > 1 Then Exit Sub
If .Row < 7 Then Exit Sub

Application.EnableEvents = False

If .Value = "" Then .Offset(0, 1).Value = ""
If .Value = "" Then .Offset(0, 2).Value = ""
If .Value = "" Then .Offset(0, 3).Value = ""
If .Value = "" Then .Offset(0, 4).Value = ""
If .Value = "" Then .Offset(0, 6).Value = ""
If .Value = "" Then .Offset(0, 7).Value = ""
End If

End Sub

 

[VBAjedi]

Give this a whirl. It uses the Intersect function, which is very useful in situations like this. Read up on it in the Help files if you are unfamiliar with how it works. This code will make sure both A & B are empty before clearing the rest of the row. If you really just want to watch column A you can adjust the second If statement accordingly:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TRange As Range, TargetRow As Integer
Set TRange = Intersect(Target, Range("A7:B1000"))
If Not TRange Is Nothing Then
   TargetRow = TRange.Cells(1, 1).Row
   If Range("A" & TargetRow).Value & Range("B" & TargetRow).Value = "" Then
      Application.EnableEvents = False
      Range("C" & TargetRow & ":Z" & TargetRow).Clear
      Application.EnableEvents = True
   End If
End If
End Sub

Let me know how that works out for you!

VBAjedi

 

[ramnewbietoaccess]

thank you very much. i will read up on that. i am unfamiliar with intersect functions but love learning new things. thanks again!