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

 

[Rick Rothstein]

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!

Hmm! I see what is causing it, but I don't have a quick fix that won't slow the code down noticeably. I have to step out shortly, so I won't be able to get back to this for a few hours... sorry... I'll see what I can do to fix it then.

 

[lorikgator]

If there's a way to fix the array issue, this is an excellent solution, so I appreciate any time you can spare on an answer to that bit. You've already done so much, though - I'm very grateful! Thank you!

 

[Rick Rothstein]

If there's a way to fix the array issue, this is an excellent solution, so I appreciate any time you can spare on an answer to that bit. You've already done so much, though - I'm very grateful! Thank you!

I am really having trouble figuring out way around visiting every cell given that we cannot know in advance whether they contain normal formulas or array-entered formulas. An idea... maybe. Are all your formulas array-entered? If not, are the formulas in any given column the same (either all normal or all array-entered)? Do any of the columns with formulas in them (within the range you specified earlier) ever have non-formula constants in them also? Are the columns with array-entered formulas in them fixed and, if so, which columns are they. The more information you can tell us about how your data within the range you specified earlier, the better able we will be able to construct an efficient solution for you.

 

[lorikgator]

It may just be that there isn't a simple way to do what I'm attempting, but maybe there's a different approach to achieve the outcome I need. Ultimately, if I can get rid of any data in cells that don't have a legitimate value (they evaluate to "") maybe there's another way to create the other piece.

This spreadsheet will be used for sales people to upload a lot of product selections to our system AND as the spreadsheet (as a template, of course) for the export of information from the same system. My thought is that if the user does the upload and gets errors back from our system, they may need to go back to the original spreadsheet and make changes and it would be good for the formulas they used to still be there, but in reality that may not be enough since they formulas for any other fields that they may now need if they make changes will be gone...

I hate for you to put any more effort into this if it still may not do what I have in mind. I'll consider this further and then respond so don't waste any more time on me at this point!

THANK YOU very much for your help!
Lori

 

[Jon Peltier]

Rick:

ActiveCell.HasFormula = True for any formula
ActiveCell.HasArray = True for array formula

 

[Rick Rothstein]

Rick:

ActiveCell.HasFormula = True for any formula
ActiveCell.HasArray = True for array formula

I know that, but I am trying to avoid visiting every cell individually to test those properties. While you can load up a variant array by assigning Range(..).Formula, you cannot do the same for FormulaArray, so the speed advantage picked up by visiting each element of an array (fast) as opposed to visiting each physical cell (slow) cannot be duplicated for array-entered formulas. Thus, I am trying to find some kind of mid-ground hybrid approach to keep the code as fast as possible. To do that, I need the answers to the questions I asked in Message #23.

 

[Rick Rothstein]

I hate for you to put any more effort into this if it still may not do what I have in mind. I'll consider this further and then respond so don't waste any more time on me at this point!

I really don't mind... we are talking about code and I love to write code. If you could answer the questions I asked in Message #23, I might still be able to construct a fast macro for you to use.

 

[Jon Peltier]

Reading a cell without selecting it can be pretty fast. The slow part is writing to a cell.

 

[Rick Rothstein]

Reading a cell without selecting it can be pretty fast. The slow part is writing to a cell.

Of course, not reading cells one at-a-time would be even faster (you almost cannot beat using arrays for reading or writing for speed).

 

[lorikgator]

I am really having trouble figuring out way around visiting every cell given that we cannot know in advance whether they contain normal formulas or array-entered formulas. An idea... maybe. Are all your formulas array-entered?

Unfortunately no, there's a mixture of array formulas and normal formulas.

If not, are the formulas in any given column the same (either all normal or all array-entered)?

The formulas in a given column will be the same for all user-entry rows on that sheet

Do any of the columns with formulas in them (within the range you specified earlier) ever have non-formula constants in them also?

Unfortunately that IS a possibility if the user chooses to enter a value rather than accept the formula result. Several formula cells/columns also have a data validation drop down that would change the cell value if the user picks from the list.

Are the columns with array-entered formulas in them fixed and, if so, which columns are they. The more information you can tell us about how your data within the range you specified earlier, the better able we will be able to construct an efficient solution for you.

They are fixed for the user once we complete the template. The user will not be adding or removing columns. The columns vary by sheet (each sheet is a product and the user is configuring the product for each location with one location on each row.) I can probably share the file, but I worry that deciphering the other things going on may make it more trouble than it's worth for you. If you'd rather do that, though, let me know.

Here's an example:
Column B (Access Name)
=IF(AND(A16<>"",BT16=FALSE),BS16,IF(AND(A16<>"",BT16=TRUE),BS16&" "&COUNTIF($BS$13:$BS16,$BS16),"")) ---- where A contains the Location Name and BT is code to check to see if the Access name has already been used. If it has, it takes the value created in BS16 (The location name with "- Access" added on), sees how many of that value already exist, then adds that number to the end. There's nothing keeping the user from entering their own Access name if they choose, but we encourage them to use the one created with the code.

Here's one that's an array (in column BR in my "helper column" section that's hidden from users)
{=IF(COUNTIF(ACCA_AccessSpeedOptions,$E16)>0,INDEX(ACC_Speed,SMALL(IF(ACCA_AccessSpeedOptions=$E16,ROW(ACCA_AccessSpeedOptions)-MIN(ROW(ACCA_AccessSpeedOptions))+1),1)),"")}
The named ranges are on another sheet and match the Access Speed value selected or entered by the user (in E16) to the valid values in the ACCA_AccessSpeedOptions array

Hopefully those are useful examples, but I can provide more if needed!

Thanks for continuing to try to make this work!