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

 

[Jonmo1]

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?

You didn't really answer my first question..

What type of value is expected to be returned by the formula when it's NOT "" ?
Text or Number, or could be either

We can use SpecialCells to our advantage here if the datatype is consistent.
If the formula normally returns text, maybe make it return 0 instead of "", then you can use spcecialcells - numbers
If the formula normally returns number, then keeping it as is returning "", then you can use specialcells - text ("" is text)

Or if the formula could return either text or number, make it return FALSE instead of "", then use specialcells - logicals

 

[Jonmo1]

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.

So if I understand this correclty...
Your formulas are returning "" basically because the cells of another column are blank (truely blank)
Right?

We can base our decision of which cells to clear by which cells in the OTHER column are empty..

Range("A:A").SpecialCells(xlCellTypeBlanks).Offset(0, 4).ClearContents

This would clear the contents of the cells in column E based on blank cells in column A.

 

[Rick Rothstein]

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...)?

For one tab at a time...

Sub MakeFormulaBlanksIntoRealBlanks()
  Dim R As Long, C As Long, UR As Variant
  UR = Intersect(ActiveSheet.UsedRange, Rows("13:" & Rows.Count)).Formula
  For R = 1 To UBound(UR)
    For C = 1 To UBound(UR, 2)
      If Left(UR(R, C), 1) = "=" Then If Evaluate(UR(R, C)) = "" Then UR(R, C) = ""
    Next
  Next
  Range("A13").Resize(UBound(UR), UBound(UR, 2)).Formula = UR
End Sub

If you tell me the names of the worksheets that you want to process, I will modify the code to iterate through them automatically for you.

 

[lorikgator]

Apologies, Jonmo1! I'd better assume it will be a mixture, though most frequently it'll be text. There will be other values (user-entered constants) on the sheet that will definitely be text, numbers, dates, etc. The concern with returning "FALSE" is user confusion. As much as I'd love to say I can just tell them to ignore those fields, they are a stubborn lot and generally ignore all instructions! (Sales people - an impatient crowd!)

 

[lorikgator]

Thanks, Rick! I actually have a subroutine that calls the routine I'm asking for help on that iterates through all of the worksheets, so as long as I pass the worksheet name (which I've been doing via (ws As Worksheet) what you've given me should be great! I'll test it out and let you know how it goes...

 

[lorikgator]

Rick, I'm getting a "type mismatch" error when the code hits an array formula that evaluates to "". Is it easy to have the If statement check for "{" as the Left value as a second criteria? A simple OR statement didn't do the trick as I still get the mismatch at ---> If Evaluate(UR(R, C)) = "" Then <---

 

[Rick Rothstein]

Rick, I'm getting a "type mismatch" error when the code hits an array formula that evaluates to "". Is it easy to have the If statement check for "{" as the Left value as a second criteria? A simple OR statement didn't do the trick as I still get the mismatch at ---> If Evaluate(UR(R, C)) = "" Then <---

Yeah, I could imagine some array-entered formulas screwing up the Evaluate function. Give this modification to the code I posted earlier a try in its place (it should still be quite fast and I think I will eliminate the problem...

Sub MakeFormulaBlanksIntoRealBlanks()
  Dim R As Long, C As Long, URformula As Variant, URvalue As Variant
  URformula = Intersect(ActiveSheet.UsedRange, Rows("13:" & Rows.Count)).Formula
  URvalue = Intersect(ActiveSheet.UsedRange, Rows("13:" & Rows.Count)).Value
  For R = 1 To UBound(URformula)
    For C = 1 To UBound(URformula, 2)
      If Left(URformula(R, C), 1) = "=" Then If URvalue(R, C) = "" Then URformula(R, C) = ""
    Next
  Next
  Range("A13").Resize(UBound(URformula), UBound(URformula, 2)).Formula = URformula
End Sub

 

[lorikgator]

Perfection! That did the trick! Super fast, indeed! Just out of curiosity, I can easily see why you chose R and C for those variable names, but what does "UR" represent? Used Range perhaps?

 

[Rick Rothstein]

Perfection! That did the trick! Super fast, indeed! Just out of curiosity, I can easily see why you chose R and C for those variable names, but what does "UR" represent? Used Range perhaps?

Yes.

 

[lorikgator]

AAAAAND I spoke too soon... now it seems to be removing the array designation from array fields? At least that's my best guess. When I evaluate the formulas in my backup spreadsheet prior to running the code, I see the full array for my named range in the formula but when I evaluate the formula in the spreadsheet where I've run the new code, it evaluates as if the named range is a single value... any thoughts? Sorry to be that annoying person that just can't seem to say "thank you" and move on!