Non Blank - Blank Cells????

[Guest]

I have a very large spreadsheet that was given to me by someone who created
it from a database. I need to do a CountA to help sort it. But the "empty"
cells, which look blank to me, are not. So the CountA counts every cell.

Would you know how I can get the cells that look empty to be seen as blank
so I can do a valid CountA?

 

[Dave O]

Here's a solution for you. Step 1, of course, is to make a backup copy
of your data so you don't lose anything! Then copy this code, and
paste it in as a macro.

It stores the value of each cell to memory if the cell does not contain
a formula, deletes the contents of the cell, and rewrites the stored
value into the cell.

Sub TrueVal_Tab()
'Converts apparently non-blank cells, such as those containing
'spaces or an apostrophe, to blank cells in the current tab of
'a workbook. Useful for data imported from a database.

'Use and distribute freely. If you find this useful, and particularly
'if you are in a corporate setting and this bails you out of a serious
'jam, ask your company to make a donation to the American Diabetes
'Association, or the national Diabetes Foundation in your country.

'Set calc to manual
With Application
..Calculation = xlManual
..MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Dim TrueVal
Dim Rows As Long, Columns As Long
Dim R As Long, C As Long

'determine lower right corner of this tab
ActiveCell.SpecialCells(xlLastCell).Select
Rows = Selection.Row
Columns = Selection.Column
'return to a1
Range("a1").Select

'Begin FOR loop to cycle through columns and rows
For C = 0 To Columns - 1
For R = 0 To Rows - 1

'if the cell contains a formula, skip
If Len(ActiveCell.Offset(R, C).Formula) > 0 Then GoTo Bailout:
TrueVal = Trim(ActiveCell.Offset(R, C).Value)
ActiveCell.Offset(R, C).Value = ""
ActiveCell.Offset(R, C).Value = TrueVal

Bailout:
Next R
Next C

'set calc to auto
With Application
..Calculation = xlAutomatic
..MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Calculate
End Sub

 

[Dave O]

I posted a response earlier- or thought I did- but it has not appeared
yet. The code in that post is buggy, and should be replaced with the
following code.

Copy this code and paste it in as a macro. It stores the value of each
cell in a tab (except cells that contain formulas) to memory, erases
the contents of the cell, and replaces the contents with the scrubbed
value: no apostrophes, blanks, etc.

Sub TrueVal_Tab()
'Converts apparently non-blank cells, such as those containing
'spaces or an apostrophe, to blank cells in the current tab of
'a workbook. Useful for data imported from a database.

'Use and distribute freely. If you find this useful, and particularly
'if you are in a corporate setting and this bails you out of a serious
'jam, ask your company to make a donation to the American Diabetes
'Association, or the national Diabetes Foundation in your country.

'Set calc to manual
With Application
..Calculation = xlManual
..MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Dim TrueVal
Dim CurrentFormat As String
Dim Rows As Long, Columns As Long
Dim R As Long, C As Long

'determine lower right corner of this tab
ActiveCell.SpecialCells(xlLastCell).Select
Rows = Selection.Row
Columns = Selection.Column
'return to a1
Range("a1").Select

'Begin FOR loop to cycle through columns and rows
For C = 0 To Columns - 1
For R = 0 To Rows - 1

'if the cell contains a formula, skip
If Mid(ActiveCell.Offset(R, C).Formula, 1, 1) <> "=" Then
TrueVal = Trim(ActiveCell.Offset(R, C).Value)
ActiveCell.Offset(R, C).Value = ""
ActiveCell.Offset(R, C).Value = TrueVal
End If

Next R
Next C

'set calc to auto
With Application
..Calculation = xlAutomatic
..MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Calculate
End Sub

 

[Dave Peterson]

You've looked at those cells and verified that the previous owner didn't put
space characters in the cells???

You can use =len(a1) to see if what's there is has a length larger than 0.

===
But if you have formulas that evaluate to ""
like:

=if(a1="ok",b1+c1,"")

And then convert to values, then this could be the culprit.

Just to verify...
Select an offending cell.
then hit tools|options|transition tab|turn on "transition navigation keys"
then ok your way out.
Look at the formula bar. Do you see an apostrophe?

If yes, then you've found the problem.

I do this (either in code or manually).

Select all the cells
edit|replace
what: (Leave empty--no space, not anything)
with: $$$$$ (some unique set of characters)
replace all

Then once more
edit|replace
What: $$$$$ (same unique set of characters)
with: (Leave empty--no space, not anything)
replace all

(Replace will limit itself to the used range, so you don't have to worry about
going to IV65536.)

Now select one of those previously offending cells.
look at the formula bar. (TaDa!)

Don't forget to turn this off:
tools|options|transition tab|"transition navigation keys"

It does stuff that most excel users don't like.