VBA to delete cell contents based upon another

L

Legacy 48831

Guest
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?
 
Last edited by a moderator:
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
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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?
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
 
Upvote 0
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
Code:
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

Code:
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
 
Last edited:
Upvote 0
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:
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
 
Upvote 0
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?
 
Upvote 0
@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
 
Upvote 0
Are you trying to amend more than one cell at once?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top