Delete if Cells are blank

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

Code:

---

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

Hi,

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

Code:

---

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!)

Quote:

---

Originally Posted by firefytr

Hi,

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

Code:

---

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.[vba] Offset(0, -2).Value = "=VLOOKUP(RC[2],Language,2,FALSE)"[/VBA]



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

Like this?

Code:

---

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

---

That did it thanks for the help

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.