• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Why is the macro deleting data that has only numbers, but not if the data contains alpha or special characters?

Eloise T

Active Member
I have a VBA macro which "cleans" up the data before I process it; however, it cleans up too well in Column D.

If the character data in any cell in Columns A-F is all numbers, the macro (CleanUp_9) deletes the data found in that cell. Fortunately, the only column that "may" have all numbers is Column D. Columns A, B, C and E and F all have other characters mixed with numbers which keeps the data from being deleted. (Please see attached file).

If the data in any cell in Column D has any other character mixed in with it, the data remains and is not deleted. Examples:
123456 will be deleted.
123 456 will not be deleted due to the space.
123456& will not be deleted due to the ampersand.
1234n56 will not be deleted due to the letter n.

The tab labeled "Justin" has a "Start VBA" button in cell F1 to make it easy to engage the macro. For demonstration purposes, the cells highlighted in yellow in each of the tabs contain only numbers and will be deleted upon engaging the macro.

I believe the macro line that is causing the deletions is Ln 84, but I don't understand why. See below:
Code:
.Value = .Parent.Evaluate(Replace("IF(#>"""",SUBSTITUTE(TRIM(CLEAN(#)),CHAR(160),""""),"""")", "#", .Address))
Thanks for your assistance in advance!
 

Attachments

Last edited:

Marc L

Excel Ninja
After a glance :​
► Codeline #84 : .Value = .Parent.Evaluate(Replace("IF(#>0,SUBSTITUTE(TRIM(CLEAN(#)),CHAR(160),""""),"""")", "#", .Address))
or this variation (my favorite) : .Value = .Parent.Evaluate("IF({1},SUBSTITUTE(TRIM(CLEAN(" & .Address & ")),CHAR(160),""""))") …​
► Codeline #21 : must be False rather than TrueAs True = On, so False = Off …
And you play my kBeep … :cool:
 

Marc L

Excel Ninja
► Codeline #501 must be obviously after codeline #510 (Next) or don't be surprised to notice some flickering (so working in slow mode)…​
► Codelines #1, 3, 4 & 5 are useless - since Excel 2010 whatever 32 or 64 bits version - add an apostrophe at the beginning of each one …​
 

Eloise T

Active Member
After a glance :​
► Codeline #84 : .Value = .Parent.Evaluate(Replace("IF(#>0,SUBSTITUTE(TRIM(CLEAN(#)),CHAR(160),""""),"""")", "#", .Address))
or this variation (my favorite) : .Value = .Parent.Evaluate("IF({1},SUBSTITUTE(TRIM(CLEAN(" & .Address & ")),CHAR(160),""""))") …​
► Codeline #21 : must be False rather than TrueAs True = On, so False = Off …
And you play my kBeep … :cool:
Both lines work! Thank you! Why is the 2nd variant your favorite?
RE: codeline #21. It's normally False. I changed it to true to help me debug.
 

Eloise T

Active Member
► Codeline #501 must be obviously after codeline #510 (Next) or don't be surprised to notice some flickering (so working in slow mode)…​
► Codelines #1, 3, 4 & 5 are useless - since Excel 2010 whatever 32 or 64 bits version - add an apostrophe at the beginning of each one …​
What does codeline #2 do?
 

Marc L

Excel Ninja
Both lines work! Thank you! Why is the 2nd variant your favorite?
Without the IF function the formula is not evaluated as an array formula, the reason why IF must be used …​
IF range > "" is a tip to force the formula to be array reliable but as you met it's a mess for the numeric values.​

So for numeric & text the Excel formula way to check if a cell is 'empty' is not > "" but > 0 or better <> 0 in case of negative values.​
My favorite is instead of checking anything to force the formula as an array one is just to place a single array {1} !​
Kudos to the guy who found first this tip, so obvious …​
Another variant to solve your issue with numerics with a kid level formula logic :​
.Value = .Parent.Evaluate(Replace("IF(#>"""",SUBSTITUTE(TRIM(CLEAN(#)),CHAR(160),""""),#)", "#", .Address)) …​
Here the mod is to replace the negative result of the IF with the range address rather than an empty string obviously,​
meaning if a cell is text do the formula and if not - so numeric - stay as it is, maybe the new favorite in your context …​
What does codeline #2 do?
This codeline declare an external function - does not exist in VBA - and without it my DemokBeep procedure can't work …​
 

Debaser

Active Member
IMO <>0 is not a good test for an empty cell, since it would treat a cell containing 0 as empty. I'd suggest the original test should simply have been for <>"" rather than >""
 

Marc L

Excel Ninja
Yes, you're right !​
But to separate text to be treated by formula from numerics to not be treated I prefer > "" like in my last variation.​
Now Eloise has a complete view …​
 
Top