Delete if Cells are blank

[mduff]

Hi all I have been Useing this code and it works quite well


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Application.EnableEvents = False
If Not Intersect(Target, Columns(5)) Is Nothing Then
Set rng = Intersect(Target, Columns(5))
Application.EnableEvents = False
For Each cell In rng
cell.Offset(0, -1).Value = "=VLOOKUP(RC[1],Language,3,FALSE)"
cell.Offset(0, -2).Value = "=VLOOKUP(RC[2],Language,2,FALSE)"
Next
rng(rng.Cells.Count)(2, 1).Select
End If
Application.EnableEvents = True
End Sub

But I was wondering if there was a way to add that if Target, Columns(5)) is empty to remove and formulas in Offset(0, -1) and Offset(0, -2) any help is really appreciated

[Zack Barresse]

Hi,

Not sure if I understand what you mean, but maybe adding a check to it ... ??


If cell.Value <> "" Then
cell.Offset(0, -1).Value = "=VLOOKUP(RC[1],Language,3,FALSE)"
cell.Offset(0, -2).Value = "=VLOOKUP(RC[2],Language,2,FALSE)"
End If

(And I added the VBA tags to your post; see what it does to the code? :) Thanks to Mark007 for that!)

[mduff]

Hi,

Not sure if I understand what you mean, but maybe adding a check to it ... ??


If cell.Value <> "" Then
cell.Offset(0, -1).Value = "=VLOOKUP(RC[1],Language,3,FALSE)"
cell.Offset(0, -2).Value = "=VLOOKUP(RC[2],Language,2,FALSE)"
End If

(And I added the VBA tags to your post; see what it does to the code? :) Thanks to Mark007 for that!)Thanks for the reply



If I understand that code right it will only add the formula if the target cell is blank what I am looking for is if the target cell is Blank (or becomes blank if data is removed from the target cell) then the formulas are removed from the ranges in the offset.




Let me try to explain a little more this sheet has a lot of information entered and removed every day and after data is removed the formulas stay because this code adds them (and takes up a lot of space). What I would like is if the data is removed then remove the formulas from the ranges that this code adds them too cell. Offset(0, -2).Value = "=VLOOKUP(RC[2],Language,2,FALSE)"



thanks for the tip on the vba tags! and let me know if you need more information

[Jacob Hilderbrand]

Like this?



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Application.EnableEvents = False
If Not Intersect(Target, Columns(5)) Is Nothing Then
Set rng = Intersect(Target, Columns(5))
Application.EnableEvents = False
For Each cell In rng
If cell.Value = "" Then
cell.Offset(0, -1).ClearContents
cell.Offset(0, -2).ClearContents
Else
cell.Offset(0, -1).Value = "=VLOOKUP(RC[1],Language,3,FALSE)"
cell.Offset(0, -2).Value = "=VLOOKUP(RC[2],Language,2,FALSE)"
End If
Next
rng(rng.Cells.Count)(2, 1).Select
End If
Application.EnableEvents = True
End Sub

[mduff]

That did it thanks for the help

[Zack Barresse]

Glad you got it working. :yes

Did you know you can mark your own thread solved here? By going to Thread Tools -> Mark Solved -> Perform Action.