Blank but not empty cells

Hi,
I have imported data from an external file into excel. Some of the cells seems to be blank but obviously not empty!!
They have length (4) and they do not "behave" as empty cells.
How can I transform them to blanks?
Eli

[waggy30]

Why arethey obviously not empty?
If they seem to be blank. Have you considered that the text may be coloured white?
And if they don't behave as empty cells, how do they behave?

[eliW]

On 2002-03-10 05:15, waggy30 wrote:
Why arethey obviously not empty?
If they seem to be blank. Have you considered that the text may be coloured white?
And if they don't behave as empty cells, how do they behave?

1) when I want to mark all empty cells - they are not marked
2) they have length =len(a1) gives 4
Eli


[ This Message was edited by: eliW on 2002-03-10 05:23 ]

[waggy30]

Sorry, I don't know

[dk]

Here's some code which will get rid of any cells which only contain space characters. I've used an array for purposes of speed and it seems to run OK. Did a 2000x15 test in 0.3 seconds.

Let me know how you get on,
D

Option Explicit

Sub ClearSpaces()
Dim rngeCells As Range, vArray() As Variant
Dim lngCol As Long, lngRow As Long

Set rngeCells = ActiveSheet.UsedRange

ReDim vArray(1 To rngeCells.Rows.Count, 1 To rngeCells.Columns.Count)
vArray = rngeCells.Value

For lngRow = 1 To rngeCells.Rows.Count
    For lngCol = 1 To rngeCells.Columns.Count
        If SpacesOnly(vArray(lngRow, lngCol)) Then
            vArray(lngRow, lngCol) = vbNullChar
        End If
    Next lngCol
Next lngRow

rngeCells.Value = vArray

End Sub


Function SpacesOnly(vValue As Variant) As Boolean
'Returns true if vValue only contains spaces
Dim lngCharloop As Long

SpacesOnly = True
For lngCharloop = 1 To Len(vValue)
    If Asc(Mid$(vValue, lngCharloop, 1)) <> 32 Then SpacesOnly = False: Exit Function
Next lngCharloop
End Function

[eliW]

Thank you D
I prefer to deal with it with formula rather then VBA code, like for example:
if(len(a1)>=4,"",a1)
Thatsolved my problem, yet I do not understand what is the problem and how can a cell contain something and be invisible
Eli

[dk]

If the cells have a length of 4 but they look blank then I'd imagine that the cell has 4 space characters in it. To get rid of cells like that with a formula is going to be tricky. In your example if(len(a1)>=4,"",a1) that would return "" for anything even the number 1234 or word Excel. Maybe I'm missing something here.

Regards,
D

[Aladin Akyurek]

On 2002-03-10 05:41, eliW wrote:
Thank you D
I prefer to deal with it with formula rather then VBA code, like for example:
if(len(a1)>=4,"",a1)
Thatsolved my problem, yet I do not understand what is the problem and how can a cell contain something and be invisible
Eli

Eli,

Text to Columns would eliminate spaces.

You can check what char(s) such cells contain:

=CODE(LEFT(A1))
=CODE(LEFT(A1,2)

etc.

Aladin

[Derek]

Hi
Could you confirm they are actually 4 spaces by using the find function? (In the find-what box just press the space bar 4 times).
If so you could do use replace function (leave the replace with what box blank)
Just a thought
regards
Derek
PS Trim formula might also do it

[ This Message was edited by: Derek on 2002-03-10 06:07 ]

[waggy30]

Yeh, Derek that would work. i just tried it