go to special blank cells

[already]

Hi

I have a lot of formulas in (if(xxx;"")) column A. Some of those results in nothing (blank cells). I did a copy > paste special > values to column B. After that I have selected column B > go to special > Blanks. And I received a message that no cells are blank (nothing is selected). I can work around this problem by selecting the cells and do a clear content and so blank cells are found and I can delete them. Why are the cells not recoginized as blank cells in first instance?

Any idea?

Thanks for your help.

Kind regards

Al

 

[VoG]

If they contain "" then they are not blank.

 

[already]

Hi Vog,

Indeed if the content is "" they are not blank. But if I do a copy > paste special "values" the content should be blank or am I wrong.

Kind regards

Al

 

[VoG]

Sorry, if you paste special as values "" remains as "" (vbNullString) and is not blank.

 

[already]

Thanks Vog,

But is there a way to remove those "not empty cells" without selecting them one by one to clear the content?

Al

 

[VoG]

You could do something like this

Sub clr()
Dim c As Range
For Each c In ActiveSheet.UsedRange
    If c.Value = "" Then c.ClearContents
Next c
End Sub

 

[Peter_SSs]

You can use AutoFilter (even on the original formula column) and Filter for Blanks as AutoFilter does see "" cells as blanks. Then you can just select all the visible cells below the headings and do whatever you want.

 

[Rick Rothstein]

If you want to select the "blank" cells, then give this a try. Select Column A (you can select a smaller range if you need to restrict which "blanks" you want to find), then press CNTL+F to bring up the Find dialog box and then click the "Options>>" button to reveal all the options available. Now, leave the "Find what" field empty and select Values for the "Look in" drop down. Now, click the "Find All" button and then press CTRL+A and finish off by clicking the "Close" button. All the "blank" cells in Column A within the UsedRange (or the smaller selection if you did that instead) should now be selected... you can do whatever you want with them.

 

[already]

Thanks all,

Vog's code seems to be the fastest way

Have a nice WE

Al

 

[pgc01]

... and another one:

Sub ReallyEmpty()
 
On Error Resume Next
With Columns("B").SpecialCells(xlCellTypeConstants, xlTextValues)
    .Value = .Value
End With
End Sub