Special cells blanks and "0"'s

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
I have several sheets that have cells with no entry and sometimes they a null or blank or ")" etc. They mess with my formulas.
So I though this would make them all the same, but it wont run. Any help is appreciated. What am I missing?
FYI - it is formulated as a table - is that why it errors out?



Sub AddSpaceBlankCells()
ActiveSheet.UsedRange.SpecialCells(xlBlanks).Value = " "
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
What is the error message?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
In that case there are no empty cells in the used range.
If a cell contains anything at all it is not blank (empty), although it may appear to be.
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
so if I can see nothing in the cell and the formula does not see it as a "" or a " " or a "0" how do I identify what is in the cell?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
Try
=code(a1)
Where A1 is an "empty" cell
What does it return?
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
I did so on several cell sand I get either 32 or #value .
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
32 denotes that that cell has a space as the first character, whilst #Value suggests that there is nothing in the cell.
Which means that your code should work.
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
I see, so my code is looking for one or the other condition -- I need to look for both.
Tks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
Your code is looking for cells that are totally & utterly blank. That is to say cells that contain nothing at all.
If that is not what you want I suggest that you start a new thread explaining exactly what you want to do.
 

Forum statistics

Threads
1,085,842
Messages
5,386,288
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top