seeking better solution to remove empty text string created by ""

[astrojack98]

I've been struggling to remove the empty text string created when I use "" as the false value in formulas. I think I found a way but I wanted to see if anyone has a more elegant solution, and one that would work as a macro.

I use the "if" formula to populate many of the cells on my spreadsheet and was using "" as the false value [ex: if(A2>0, "YES","")], and I need false cells to truly be empty. I learned from several threads on this site that the false cells are not truly empty; the equation leaves an empty text string in the false cells.

My current "work around" is to populate all false cells with "z" [ex: if(A2>0,"YES","z")]. I then use "find all" to find all cells with "z" (matching exact cell contents), hit "ctrl+A" to select all the "z" cells, and then hit "delete". This works - all the false cells will truly be empty, but it takes time and I don't think I can make it into a macro. Does anyone know of a better solution?

 

[VoG]

Maybe this

Sub makeblank()
Dim c As Range
For Each c In ActiveSheet.UsedRange
    If c.Value = "" Then c.ClearContents
Next c
End Sub

 

[Jonmo1]

Welcome to the board

You might just let your IF formulas actually return the FALSE Boolean value.
=IF(A2>0,"Yes") <-Notice the False section is just omitted, it defaults to FALSE.
Then you can just use Autofilter to filter for FALSE values, and delete the rows.
And that can be put into a macro.

 

[Rick Rothstein]

I've been struggling to remove the empty text string created when I use "" as the false value in formulas. I think I found a way but I wanted to see if anyone has a more elegant solution, and one that would work as a macro.

I use the "if" formula to populate many of the cells on my spreadsheet and was using "" as the false value [ex: if(A2>0, "YES","")], and I need false cells to truly be empty. I learned from several threads on this site that the false cells are not truly empty; the equation leaves an empty text string in the false cells.

My current "work around" is to populate all false cells with "z" [ex: if(A2>0,"YES","z")]. I then use "find all" to find all cells with "z" (matching exact cell contents), hit "ctrl+A" to select all the "z" cells, and then hit "delete". This works - all the false cells will truly be empty, but it takes time and I don't think I can make it into a macro. Does anyone know of a better solution?

Give this macro a try...

Sub ClearEmptyStringCells()
  Dim R As Long, C As Long, FormulaArray As Variant, ValueArray As Variant
  ValueArray = ActiveSheet.UsedRange
  FormulaArray = ActiveSheet.UsedRange.Formula
  For R = 1 To UBound(ValueArray)
    For C = 1 To UBound(ValueArray, 2)
      If ValueArray(R, C) = "" Then FormulaArray(R, C) = ""
    Next
  Next
  ActiveSheet.UsedRange.Formula = FormulaArray
End Sub