Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Blank but not empty cells

  1. #1
    Guest

    Default

    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

  2. #2
    New Member
    Join Date
    Mar 2002
    Location
    England
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  3. #3
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    England
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry, I don't know

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Code:
    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

  6. #6
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    New Member
    Join Date
    Mar 2002
    Location
    England
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yeh, Derek that would work. i just tried it

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •