Blank cells not considered blank by Excel- need help in putting a 0 in all blank cells

[GVRY]

I have a problem with data I downloaded from a government website. There are cells with numbers which I was able to convert to numbers from text but there are some cells which are blank and I want a 0 in all the blank cells. Excel doesnt consider the blank cells to be blank but treats them as text or general.
I tried the solutions suggested by some on the other forums asking similar questions but nothing seemed to help (like pasting a 1 in one of the cells and multiplying it with these cells or find go to special blanks also doesnt help). I know those cells are not blank but have some text or something like that which is not visible. And I have a lot of data ( 17 years, 10 countries (one file for each country and one sheet for each year) and each sheet has over 5000 rows) so I cannot manually enter 0 for all the blanks. I just dont know what to do. Please help.

 

[jolivanes]

Could you try this on a copy of your workbook and see if the cells become empty?
Once they are empty you can easily put 0 (zeros) in the empty cells with "Replace or something.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Do Same with carriage return (Alt-Enter)
'Also Treat CHR 010, as a space (CHR 032)
Selection.Replace What:=Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

For the above macro you need to select a range.
For the following macro you'll need to change the range in the code.

Sub Replace_Empties()
Application.ScreenUpdating = False
    With Range("A1:AV40")
        .Replace "", 0
    End With
Application.ScreenUpdating = True
End Sub

Make sure to try all of this on a copy though. You never know!!!!

 

[DavidCoop]

Did you know there is a CLEAN() function in Excel?

You could try =VALUE(CLEAN(A1)&0)

This would need to be in an adjacent column, and a bit messy, so the VBA solutions jolivanes mentioned may be more practical!

 

[GVRY]

Thank you for your responses. The macros doesnt seem to run on the file. Not sure where I am making a mistake. The error message is "Object variable or With block variable not set." Not sure what that means.

I tried the value clean option. It worked in the sense that it replaced the blanks with "#VALUE!" but when I try to find replace #Value!, it isnt finding it. WHat I observed is that when I find, it is allowing me to look in values but the moment I click on replace, it is changing the "Look in" option to formula and not giving any other options. Please let m eknow where I am going wrong.

Thanks much.

 

[Aladin Akyurek]

You might want to try...

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, <ACRONYM title="visual basic for applications">VBA</ACRONYM> does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub