Clear ONLY cells that contain a formula that evaluates to "" using VBA

[lorikgator]

I've searched rather extensively but can only seem to find the OPPOSITE of what I'm trying to.


I want to use VBA to evaluate all of the cells in a given range on a sheet and delete the contents of any cells that have formulas that evaluate to ""


We are using this spreadsheet to upload to a system that uses XML to decide which columns to read on import. If it sees a value, it imports it, and if that value is "", it is of course recognizing the field as having content instead of being blank (because of course the content is ""). I need to get rid of all of the fields that evaluate to "" but, ideally, keep the formulas for the fields that evaluate to something other than "". I've been using "CLEAN" to cheat and just essentially copy and paste the values of the cells (and remove the ""), but I'd really like to keep the formulas for cells that evaluate to something useful if possible.


Any idea how this could be done in VBA?


Here's my "clean" code that's working fine. I was hoping maybe just an "if" statement to target only cells with a value of "" but I can't seem to get the syntax right, assuming that's possible:

Sub DeletingBlankFormulas()

Dim rng As Range
    With ActiveSheet
        Set rng = Application.Intersect(.UsedRange, .Cells.Resize(.Rows.Count - 12).Offset(12))
        rng.Value2 = Evaluate("IF(ROW(" & rng.Address & "),CLEAN(" & rng.Address & "))")
        On Error Resume Next
    End With
End Sub

Help much appreciated!
Lori

 

[jim may]

.. Wrong answer given

 

[Jon Peltier]

I'd try something like this:

Sub DeleteNullFormulas()
  Dim rng As Range
  For Each rng In Selection.Cells
    If rng.HasFormula And Len(rng.Text) = 0 then
      rng.Formula = ""
    End If
  Next
End Sub

 

[jim may]

Excel 2012
	A	B	C	D	E	F
1						<< These Yellow Cells have formula
2						which all equate to "".
3		123	3	3
4		456	2			Before Running Macro "Foo" Select/Highlight
5		789	8			the Range of Cells B3:D5 then Run Macro "Foo".
Sheet1

Cell Formulas
Range	Formula
D3	=IF(B3/3<>41,"",B3/41)
D4	=IF(B4/3<>40,"",B4/40)
D5	=IF(B5/3<>40,"",B5/40)

Sub Foo()
With Selection.Cells  'Cells B3:E5 should be selected
    For Each C In .Cells
    If C.Value = "" Then
    If Left(C.Formula, 1) = "=" Then
        C.ClearContents
    End If
    End If
    Next C
End With

End Sub

 

[Jonmo1]

What type of value does the formula return if it's NOT "" ? Number or Text (or could be either) ?

Would you be open to makeing the formula return something other than "" ? like #N/A?
That could be used to clear the cells in 1 step, rather then having to loop through each one.

 

[Rick Rothstein]

I think this macro will faster that the other offerings (which could be of some importance if you have a lot of cells to process)...

Sub MakeFormulaBlanksIntoRealBlanks()
  Dim R As Long, C As Long, UR As Variant
  UR = ActiveSheet.UsedRange.Formula
  For R = 1 To UBound(UR)
    For C = 1 To UBound(UR, 2)
      If Len(UR(R, C)) Then If Evaluate(UR(R, C)) = "" Then UR(R, C) = ""
    Next
  Next
  ActiveSheet.UsedRange.Formula = UR
End Sub

 

[lorikgator]

Jonmo1 - that MIGHT be possible... trying to think through the possibilities. In essence, the users of this spreadsheet (for bulk processing of data to create a price quote based on customer criteria in a request for proposal) will either be copying and pasting some data or selecting it from drop downs and I've created dependent fields (populated via formula) to save some work for them based on the initial entry info. They are able to enter up to 1000 rows of data and all of those rows are pre-populated with the formulas, just awaiting entry in the first few columns. I can have the formula cells potentially have a "default" value of '-' or something, but it needs to be something that won't potentially be a value in a valid cell and won't confuse the user (like #N/A likely would.) If I used a standard default value, what would the one step to clear all of those cells?

 

[lorikgator]

Rick, speed is indeed important as this will impact up to 1000 rows in 20 tabs with 15-20 columns each. I need to be sure to start at Row 13 as I have a copy of the entire row with the correct formulas hidden above row 13 so we can repopulate the formulas and data validation if needed. How can I modify your code to start the used range check at Row 13, or does it make more sense to define my range since I know it'll be rows 13 to 1013 for all sheets (at least for now...)?

 

[lorikgator]

Thank you, Jim! The code will need to select the used range (or explicitly select rows 13 to 1013 and all of the associated columns) for all 20 spreadsheets, so it'll need to include the ability to identify the affected cells preferably without selecting them individually or even possibly as an array because I know selecting content adds a lot of time and work to the macro. I like the simple method for identifying the cells that are blank, though, and even if that doesn't work for this scenario I think I'll be using that elsewhere!

 

[lorikgator]

Jon, that LOOKS like an elegant solution! I'll check what Rick supplied for the potential for one being more efficient than the other, but it makes perfect sense to my limited VBA knowledge!