VBA to delete cell contents based upon another

[Legacy 48831]

Hello everyone. I have been searching through the forums looking for an example to delete the contents of a cell based upon the value in another but cannot seem to get it to work.
What I have is a spreadsheet that in A15 if it is blank, I would like to have E15 to delete it's contents. If A15 is anything else then I want the user to be able to enter a number in E15, otherwise if they clear A15 then E15 clears.

The major issue is that I need this to work not only on A15 and E15 but from A15:A43 and from E15:E43.

Is there a simple method of doing this with VBA, since it would be only clearing a range within the column instead of a full column?

 

[offthelip]

These two sub routines together will do it, you mneed to put them in sheet module for that worksheet

Private Sub Worksheet_Activate()
inarr = Range("a15:A43")
Application.EnableEvents = False
For i = 1 To 29
 If inarr(i, 1) = "" Then
   Cells(i + 14, 5) = ""
 End If
Next i
Application.EnableEvents = True


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("a15:A43")) Is Nothing) Then
Application.EnableEvents = False
Target.Offset(0, 4) = ""


End If
Application.EnableEvents = True




End Sub

 

[Legacy 48831]

Thank you very much offthelip, That worked perfectly!

 

[Legacy 48831]

I do have one more question that I just realized would be an issue. If the user inserts a row, the code does not automatically update and presents an error. Is there a method that could be used to allow for inserting a row and automatically updating the vba code to add the new line or lines that the user adds?

 

[offthelip]

There is no easy way to detect when a user inserts a line, so I think the only way you get around that is to automatically detect the last row that has got any data in it in column E:

Private Sub Worksheet_Activate()
lastrow = Cells(Rows.Count, "E").End(xlUp).Row
If lastrow > 15 Then
inarr = Range("a15:A" & lastrow)
Application.EnableEvents = False
For i = 1 To lastrow - 15
 If inarr(i, 1) = "" Then
   Cells(i + 14, 5) = ""
 End If
Next i
Application.EnableEvents = True
End If


End Sub




Private Sub Worksheet_Change(ByVal Target As Range)
lastrow = Cells(Rows.Count, "E").End(xlUp).Row
If Not (Intersect(Target, Range("a15:A" & lastrow)) Is Nothing) Then
Application.EnableEvents = False
Target.Offset(0, 4) = ""




End If
Application.EnableEvents = True








End Sub

 

[Yongle]

an aside .......

There is no easy way to detect when a user inserts a line

@offthelip
- it is possible to use a Named Range for this
- Excel re-references a named range automatically (when rows inserted \deleted above the range)
- Excel re-sizes a named range automatically (when rows are inserted\deleted inside the range)
- these features can be exploited in VBA

To illustrate ..
Place code below in sheet module
Create a named range with name MyRange
Insert\delete rows inside MyRange & right-click any cell
Insert\delete rows above MyRange & right-click any cell

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim rng     As Range:   Set rng = [MyRange]
    Cancel = True
    MsgBox rng.Address(0, 0) & vbTab & rng.Rows.Count & " rows", vbOKOnly, "MyRange"
End Sub

Notes
- can develop further to detect rows inserted below the named range
- above technique is not relevant where user is simply selecting the next available row and entering data

 

[Legacy 48831]

Hi Yongle,

I did as you explained however did you mean to add the code you posted to the code that offthelip posted?

 

[Yongle]

did you mean to add the code you posted to the code that offthelip posted?

NO!
- I said at the top that my post was "an aside"
- and at end I added "above technique is not relevant where user is simply selecting the next available row and entering data"
- it is not meant to be a suggested solution for you but does provide one method (for anyone else reading) to handle user row insert\delete

Did you test solution provided by @offthelip in post#5 ?

 

[Legacy 48831]

Hello Yongle, Sorry for the confusion. I did not understand what you meant by "an aside". Also the user would not be selecting the next available row and entering data.

The purpose is to allow certain individuals to use a "template" to calculate a cost for an assembly. This individual would have a pre-built template but may need to insert rows to be able to have enough for the configuration.

Yes, I did try the #5 solution provided by offthelip but when inserting the row I receive a runtime error '1004' which I was going to mention when I saw your post. The error has something to do with the Target.Offset

 

[Yongle]

Yes, I did try the #5 solution provided by offthelip but when inserting the row I receive a runtime error '1004' which I was going to mention when I saw your post. The error has something to do with the Target.Offset

Try inserting this as the first line of Worsheet_Change

    If Target.CountLarge > 0 Then Exit Sub