VBA code to clear contents of all cells that look blank in a range

[parkerbelt]

I'm trying to find the vba code to "clear contents" - the equivalent of pressing the delete button, for all of the cells that look like they are blank in the range ("H6:0" & lastrow).

I have a cell that has something in it, but it doesn't look like it.
If I delete the contents of the cell, my macro works correctly.

 

[Rick Rothstein]

I have a cell that has something in it, but it doesn't look like it.

Can you tell us what that "something" is? Is it a formula display ""? Or is it just some "invisible" text constant? If the latter, you can run this in the Immediate Window to get the ASCII codes and then tell us what the codes are (select one of your blank cells first)...

For X = 1 To Len(Selection): ? Asc(Mid(Selection, X, 1)): Next

 

[parkerbelt]

Can you tell us what that "something" is? Is it a formula display ""? Or is it just some "invisible" text constant? If the latter, you can run this in the Immediate Window to get the ASCII codes and then tell us what the codes are (select one of your blank cells first)...

For X = 1 To Len(Selection): ? Asc(Mid(Selection, X, 1)): Next

The cell is just blank. I tried changing the color of the font and the background and the formatting and I don't see anything in the cell.
I'm not sure how to use the immediate window like you are saying, but I clicked in the cell that is giving me the trouble, opened the VBA editor and opened the immediate window and pasted your code into the window and then put my cursor after the "Next" and hit enter and 32 showed up after your code. I guess I did it correctly and the code is 32.

 

[Rick Rothstein]

The cell is just blank. I tried changing the color of the font and the background and the formatting and I don't see anything in the cell.
I'm not sure how to use the immediate window like you are saying, but I clicked in the cell that is giving me the trouble, opened the VBA editor and opened the immediate window and pasted your code into the window and then put my cursor after the "Next" and hit enter and 32 showed up after your code. I guess I did it correctly and the code is 32.

Your cells are not blank... they have a space character in them. Give this code at try...

Sub ClearBlanks()
  Range("H6", Cells(Rows.Count, "H").End(xlUp)).Replace " ", "", xlWhole
End Sub

Note that you can do the same thing manually by selecting Column H, pressing CTRL+H to bring up the Replace dialog box, put a single space in the "Find what" field, clear the "Replace with" field, click the "Options>>" button and put a check mark in the checkbox labeled "Match entire cell contents", then click the "Replace All" button.

 

[parkerbelt]

You da man once again Rick! Thanks for your help!!

 

[TomDrury89]

Rick,

I am having a similar issue over multiple columns.

I have 4 columns that should balance to zero after a subtraction formula. The data is exported as minutes/time and i am having issues with the blanks cells causing a #value to return. If i hit backspace on a blank cell the formula will work, or if I highlight and right click clear it will also return a value, however there does not seem to be a space. I have tried Ctrl H to replace " " to "" with no luck. Is there anything else that could be effecting this orther then a space. The cells look completely empty?

Please note, there could be blank cells throughout the 4 columns.

 

[Peter_SSs]

If you put these formula in a vacant cell & point them at one of the problem 'blank' cells, what do they return?

=CODE(B5)
=LEN(B5)

 

[TomDrury89]

If you put these formula in a vacant cell & point them at one of the problem 'blank' cells, what do they return?

=CODE(B5)
=LEN(B5)


CODE(B5) = 52
LEN (B5) = 5

 

[Peter_SSs]

Are you sure that is pointed at a problem 'blank' cell? That is, does cell B5 appear blank.
Code 52 indicates that the cell's first character is a digit 4.

 

[TomDrury89]

Are you sure that is pointed at a problem 'blank' cell? That is, does cell B5 appear blank.
Code 52 indicates that the cell's first character is a digit 4.

Sorry My Error

Code, i get #value
Len = 0