Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formulas returning a value of nothing (Excel 2003)

    I want to use the "Go To -> blanks" function, but all the blanks I want to find are actually formulas that are returning a value of nothing... My eventual goal is to delete all rows that have a "blank" (have no data) in a particular column.
    Is there a quick & dirty way to convert functions returning blank values to an actual empty cell?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formulas returning a value of nothing (Excel 2003)

    Welcome to Woody's Lounge!

    You could create this macro:
    <code>
    Sub ClearBlanks()
    Dim oCell As Range
    For Each oCell In Selection
    If Trim(oCell.Value) = "" Then
    oCell.ClearContents
    End If
    Next oCell
    End Sub
    </code>
    Select the range of cells that includes the formulas returning nothing, then run the macro.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: formulas returning a value of nothing (Excel 2003)

    Use the NA() function in place of the "" in the IF expression. Then search for Error Formulas in the GoTO Special. This will pick up the NA errors and you can then delete rows based on that selection.
    Regards,
    Rudi

  4. #4
    New Lounger
    Join Date
    Feb 2008
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formulas returning a value of nothing (Excel 2

    Awesome, thanks! I tried both ways, both work perfectly!
    As it turns out, as I was trying out your solutions, I also found the easiest of ways to do this....
    Simply copy the group of cells in question onto a new sheet or even in an available column, and paste special -> values only. DONE!!!

    Thanks again!

Posting Permissions

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