Variable for "nspace"..." "; " "; " "..

[Xceller]

Hi All,
I have a report downloaded from a mainframe system that has blank cells in column A. These are not really blank cells or NullValueString. Some of them have 1 space others have 2 or 3....up to 8 spaces.

I want to set up a variable to loop through them, something like nSpance = len(cells(i,1)). This would not work because the cells that are with contents would also return a number. Thanks in advance for your help.

Here is my code without the variable:

Sub VariableSpace()

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To FinalRow

If Cells(i, 1).Text = " " Then
Cells(i, 1).Value = "One space"
ElseIf Cells(i, 1).Value = " " Then
Cells(i, 1).Value = "Two spaces"
ElseIf Cells(i, 1).Value = " " Then
Cells(i, 1).Value = "Three spaces"
End If

Next i

End Sub

 

[shg]

One way:

Sub VariableSpace()
    Dim cell        As Range
 
    For Each cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
        With cell
            If Len(.Text) > 0 And _
               Len(WorksheetFunction.Trim(.Text)) = 0 Then
                .Value = Len(.Text) & " spaces"
            End If
        End With
    Next cell
End Sub

 

[Xceller]

Shg,

Thank you so much for the code. It works flawlessly. Thanks again!

 

[shg]

You're welcome, glad it worked for you.

 

[Rick Rothstein]

If Len(.Text) > 0 And Len(WorksheetFunction.Trim(.Text)) = 0 Then

Why did you choose to use the WorksheetFunction's TRIM function instead of VB's built-in Trim function? Wouldn't this work as well?

If Len(.Text) > 0 And Len(Trim(.Text)) = 0 Then