making sure empty cellsa are empty?

[Todd]

Hi, I am finding lots of cells that look empty but
aren't? They are blank but interfere with formulas until I
go and press delete (on an empty cell!) Whats going on
and can I do something to speed this up? I am looking
forward to hours of pressing delete!


Thanks,

Todd

 

[acw]

Todd

Have a look at the length of the cell. If it is >0 then
see if it contains space(s), or some other non printing
charater. If so, then copy the contents and do an edit
replace all with nothing.

Tony

 

[David McRitchie]

Hi Todd and Tony,

Length of cell be zero for
=IF(A1=B1,"","")
'

You can use Edit, Goto, [Special], blanks

You could use a macro to go through the cells,
don't know if you want to do only constants that look
empty -- would be a safer assumption as it will not destroy
formulas or dependencies on other formulas.

Sub removeConstantsThatLookEmpty()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim cell As Range
On Error goto done '-- in case no cells selected

'-- alternative code
'-- For Each cell In Cells.SpecialCells(xlCellTypeConstants)

For Each cell In Selection.SpecialCells(xlCellTypeConstants)
If Trim(cell) = "" Then
cell.ClearContents
cell.Interior.ColorIndex = 38 '--visualize for testing
End If
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

I like to use selection. so that macros are more generic, but
if you want it to always do the entire sheet you can change that
to cells. and it will use the entire sheet. For testing purposes
I have colored the changed cells.

Instructions to install and use a macro on my
http://www.mvps.org/dmcritchie/excel/getstarted.htm

After running the macro you can use the following to provide a
more complete picture:
Ctrl+A followed by
Ctrl+G which is Edit, GoTo,
[Special]
(checkmark) blanks
Remember cells outside the used range will not be selected