Consulting

Results 1 to 6 of 6

Thread: Solved: Delete if Cells are blank

  1. #1
    VBAX Regular
    Joined
    Oct 2004
    Posts
    65
    Location

    Solved: Delete if Cells are blank

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

    [VBA]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[/VBA]

    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
    We are living in a world today
    where lemonade is made from
    artificial flavoring and furniture polish
    is made from real lemons...
    Alfred E Newman

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    Hi,

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

    [vba] 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[/vba]

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

  3. #3
    VBAX Regular
    Joined
    Oct 2004
    Posts
    65
    Location
    Quote Originally Posted by firefytr
    Hi,

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

    [vba] 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[/vba]

    (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
    We are living in a world today
    where lemonade is made from
    artificial flavoring and furniture polish
    is made from real lemons...
    Alfred E Newman

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Like this?
    [VBA]
    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 [/VBA]

  5. #5
    VBAX Regular
    Joined
    Oct 2004
    Posts
    65
    Location
    That did it thanks for the help
    We are living in a world today
    where lemonade is made from
    artificial flavoring and furniture polish
    is made from real lemons...
    Alfred E Newman

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •