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:
Thanks for your assistance in advance!
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))
Attachments
Last edited: