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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. Windows
What is the error message?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
53,139
Office Version
  1. 365
Platform
  1. Windows
Try
=code(a1)
Where A1 is an "empty" cell
What does it return?
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322

ADVERTISEMENT

I did so on several cell sand I get either 32 or #value .
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,139
Office Version
  1. 365
Platform
  1. 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
53,139
Office Version
  1. 365
Platform
  1. 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top