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.[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

[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.

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