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?

 

[Yongle]

Hopefully the above "fixed" your issue


If .... "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"

Then ...(depending on the complexity of the template) ....consider controlling row insertion by macro which asks the user how many rows are required AND auto-inserting those rows in the correct area with appropriate formatting, values and formulas in place

 

[Legacy 48831]

Hello Yongle, I inserted it but then the cell does not clear when testing. This is what I have, maybe I placed your code in the wrong place?

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)
If Target.CountLarge > 0 Then Exit Sub
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]

I think the code failing earlier caused EnableEvents property to be set to FALSE
- closing and reopening the workbook can reset it to TRUE

or run this simple sub to reset things

Sub EE()
  Application.EnableEvents = True
End Sub

It is good practice to force Application.EnableEvents to return to TRUE with a single exit point from the macro which does just that whether the code fails or not

Sub NameOfSub

[COLOR=#006400][I]Somewhere before errors can occur[/I][/COLOR]
  On Error GoTo Handling  
[I][COLOR=#006400]then[/COLOR][/I]  
  Application.EnableEvents = False
[COLOR=#006400]then [/COLOR]
  The code which may fail goes here
 
[I][COLOR=#006400]and finally one exit point out of the sub where you may want to provide details of the error etc[/COLOR][/I]
Handling[B]:  [/B][I][COLOR=#006400]( there is a colon after Handling) [/COLOR][/I]
  Application.EnableEvents = True

End Sub

 

[Legacy 48831]

Hi Yongle, The code you sent seemed to work in regards to allowing for the row to be inserted without the error however the cell does not clear when removing the contents from the initial cell. No error is returned, just leaves the quantity in the cell in the E column. Any thoughts? This is the current code:

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)
If Target.CountLarge > 0 Then Exit Sub
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
Sub EE()
  Application.EnableEvents = True
End Sub

 

[Yongle]

Please help me replicate - the code works for me, so you must be doing something different

Are complete rows being inserted or are you inserting cells?

 

[Legacy 48831]

I am inserting complete rows.

 

[offthelip]

@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

Thanks Yongle, I hadn't thought of doing it that way, another tool in the box.
Thanks also for progressing this thread

 

[Yongle]

Are you trying to amend more than one cell at once?

 

[Legacy 48831]

No, just one at a time. I also created a new workbook with no contents, no formatting, no merged cells and entered data in cell A15 and a number in Cell E15 cleared A15 but E15 continues to show what I had entered. I must be doing something simple incorrectly that is causing the issue but I am not sure what.

 

[Yongle]

what happens in rows 16 onwards?