Apparently empty (blank) cells aren't empty

[daymaker]

I exported an access database to an excel spreadsheet. In one of my columns, I need to select or 'go to' all blank fields so that I can then copy data from above field into them. Problem is, the fields (shown below) appear blank, but they aren't. So when I select the column and choose 'Edit | Go To | Special | Blanks | Ok', no blank fields are recognized.


-- removed inline image ---


I read on another site's thread that possibly when exporting empty fields in access file to excel, there is in fact a null string there rather than it actually being empty and this may be spaces.

I am hoping there's a way to possibly identify the true contents of these supposedly blank cells and then to truly empty/clear the contents. Or, I could find all cells that have these invisible characters and 'go to' them & then do my copy step. Any ideas?

Thanks, John

 

[erik.van.geit]

Sorry, as I'm sure you guessed, I meant: select the cells, invoke Text to Columns and press directly Finish in the first panel

That does not work for me, neighter manually nor by code (as a matter of fact) using XL2003
What do you get in the second MsgBox using this code?

Sub ReallyBlank()
'Erik Van Geit
'120216
    With Range("A1:A15")
    .Formula = "="""""
    .Item(5).ClearContents 'so cells A5 is truly blank
    .Copy
    End With
 
    With Range("B1:B15")
    .PasteSpecial xlPasteValues
    'expecting B1:B15 but only getting B5
    MsgBox .SpecialCells(xlCellTypeBlanks).Address
    '.Value = .Value
    .TextToColumns Destination:=.Cells(1, 1), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
    'now getting B1:B15
    MsgBox .SpecialCells(xlCellTypeBlanks).Address
    End With
End Sub

Or did I miss something?

 

[pgc01]

Hi Erik

Sorry that I did not answer before, but I wanted to try in on a older version of excel, and now I tried it on a excel 2000.

You are right. I did some tests and the Text to Columns has worked some times and others not, and so it's not to be considered as a solution.

I think your solution in post #3 is the one to use.

 

[erik.van.geit]

Hi Erik

Sorry that I did not answer before, but I wanted to try in on a older version of excel, and now I tried it on a excel 2000.

You are right. I did some tests and the Text to Columns has worked some times and others not, and so it's not to be considered as a solution.

I think your solution in post #3 is the one to use.

This is a forum, so we know that answers can come late
thanks for trying, Pedro

I tried some different ways manually and thusfar no succes.
Yes, just one: F2 enter F2 Enter F2 Enter .......

 

[rein26]

Sub test()
With Selection
.Value = .Value
End With
End Sub

Glad I found this code. Fixes an issue I encounter frequently. Thanks!

 

[Special-K99]

Hi Special-K99

Remark: Len() will only tell you if the cell has characters, not if it's empty, for that you must use IsBlank(). In this case, since Erik's code solved the problem, the cells would have empty strings. Since the cells were not empty the Go To Blanks would not work.

I define an empty cell as having no characters at all, hence the LEN() reference. To me a cell containing a space is not an empty cell since it contains an ASCII character.

 

[pgc01]

I define an empty cell as having no characters at all, hence the LEN() reference. To me a cell containing a space is not an empty cell since it contains an ASCII character.

Hi

I agree, but Len() is not enough to define if the cell is empty.

The cell may have a null string. In that case it has no characters but is not empty.

You can test it

in the worksheet with: ISBLANK()
in vba with: IsEmpty()

You'll see that if the cell has a null string both give you false, confirming that the cell is not empty.

 

[tmjoss]

Hi Erik,

This code is really useful but converts numbers I have saved as text into numbers (e.g. 0176 becomes 176 and 0088 becomes 88).
I need to keep them as text to retain the zeros, is this possible?

Thanks
Tom

 

[Mr_Lucky]

I have a similar issue in excel.

One of the calculations in my workbook is referencing a cell that contains a formula but the cell is blank. Excel seems to evaluate the blank cell with a formula as true when it should be false.

The only option seems to be to delete the formula in the blank cells or put random value "@" in the blank cells, then find "@" replace with blank. Both of these workarounds does the trick but I would prefer not to have to do this manually.

Any suggestions would be most welcome

 

[MARK858]

A cell with a formula isn't a blank cell, what are you trying to do with the cell afterwards?

 

[pgc01]

Excel seems to evaluate the blank cell with a formula as true when it should be false.

The cell is not empty, you said yourself it has a formula.

You forgot to post the formula that you are using.