Results 1 to 3 of 3

Thread: Excess Spaces

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excess Spaces

    Please see attached. This is a portion of a file I downloaded from an application. I would like vba that would get rid of the extra spaces in the jighlighted (and other) cells. Any help?Thanks in advance
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    J.L.,

    This code will do the trick I think?
    Code:
    Option Explicit
    
    Sub KillSpaces()
      
       Dim lMaxRow  As Long
       Dim lCurRow  As Long
       Dim lRowCnt  As Long
       Dim lCurCol  As Long
       Dim zCurStr  As String
       
       Application.ScreenUpdating = False
       lMaxRow = Rows().Count
       lCurCol = ActiveCell.Column()  'Start With cursor in column to process!
       
       Cells(lMaxRow, 1).End(xlUp).Select
       lRowCnt = ActiveCell.Row()
       
       For lCurRow = lRowCnt To 1 Step -1
          zCurStr = Cells(lCurRow, lCurCol).Value
            Debug.Print "Before: " & Format(Len(zCurStr), "#####")
          If WorksheetFunction.IsText(zCurStr) Then
            If Left(zCurStr, 1) = Chr(10) Then _
               zCurStr = Right(zCurStr, Len(zCurStr) - 1)
            Cells(lCurRow, lCurCol).Value = Trim(zCurStr)
            Debug.Print "After: " & Format(Len(zCurStr), "#####")
          End If
           
       Next lCurRow
       
    End Sub
    Notes:

    1. There were not only spaces but rather the 1st character in the cell was a line feed CHAR(10).
    2. Note this was also true of the numbers in the sample, they were actually text because of this. They will be converted to true numbers by this code.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks RG

Posting Permissions

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