Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Washington State (we moved)
    Posts
    16
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Delete leading/trailing spaces from a range. Excel 2003 VBA

    Hi. I need to make a VBA macro to assign to a button in Excel 2003, that removes leading and trailing spaces from a range of cells (B4:L) in a worksheet. It looks like Trim() is what I need, but getting it to do said range in VBA is past my ability.

    Thinking something like this as a starting point, based on other modules in the workbook, just not sure what to do between protect and unprotect...

    Sub RemoveSpaces()

    Dim wSheet As Worksheet

    Set wSheet = Worksheets("Call Sheet")

    With wSheet

    wSheet.Unprotect

    .Range ("B4:L")

    wSheet.Protect

    End With

    Set wSheet = Nothing

    End Sub

    Any help would be greatly appreciated.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    T.Roo,

    Here's some code to try out:
    Code:
    Sub RemoveSpaces()
    
       Dim wSheet  As Worksheet
       Dim celCntr As Range
    
       Set wSheet = Worksheets("Call Sheet")
    
       With wSheet
           If ActiveWorkbook.ProtectStructure Then _
             ActiveWorkbook.Protect Structure:=False, Windows:=False
           If .ProtectContents Then _
            .Protect DrawingObjects:=False, Contents:=False, _
                     Scenarios:=False, userinterfaceonly:=False
    
    'You need to provide a value for xx 
           For Each celCntr in .Range("B4:Lxx")
              .value = Trim(.value)
           Next celCntr
    
        If Not .ProtectContents Then _
          .Protect DrawingObjects:=True, Contents:=True, _
                   Scenarios:=True, userinterfaceonly:=True
        If Not ActiveWorkbook.ProtectStructure Then _
          ActiveWorkbook.Protect Structure:=True, Windows:=True
    
       End With
    
       Set wSheet = Nothing
    
    End Sub
    Of course you can adjust the parameters in the protection routines to suit your needs. You can also do away with the Workbook protection statements if you don't want them.

    Warning this is AIR code, e.g. untested as is.
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Alternately, you can try :

    Code:
    Sub RemoveSpaces()
    Dim cell As Range
    With ActiveSheet
    .Unprotect
    For Each cell In .UsedRange
        cell = Trim(cell)
    Next cell
    .Protect
    End With
    End Sub
    HTH,
    Maud

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Washington State (we moved)
    Posts
    16
    Thanks
    3
    Thanked 0 Times in 0 Posts
    RetiredGeek, thank you for your response.

    I am getting a runtime error '424; Object Required.

    Sounds ominous!

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Washington State (we moved)
    Posts
    16
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Maudibe, thank you also for your reply.

    With yours, I get a Runtime Error '13': Type Mismatch.

    Not sure of the effect on yours or RetiredGeeks code, but I note that I did not mention in my original post that one column is date, several are text, and one is number.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    I amended Maud's nice code. Try this..
    Code:
    Sub RemoveSpaces()
    Dim cell As Range
    With ActiveSheet
    .Unprotect
    on error resume next
    For Each cell In .UsedRange
        cell = Trim(cell)
    Next cell
    .Protect
    End With
    on error goto 0
    End Sub
    zeddy
    Last edited by zeddy; 2015-06-29 at 17:15.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by Tropicalroo View Post
    RetiredGeek, thank you for your response.

    I am getting a runtime error '424; Object Required.

    Sounds ominous!
    Sorry about that should have tested it! Here's the corrected code.
    Code:
    Option Explicit
    
    Sub RemoveSpaces()
    
       Dim wSheet  As Worksheet
       Dim celCntr As Range
    
       Set wSheet = Worksheets("Call Sheet")
    
       With wSheet
           If ActiveWorkbook.ProtectStructure Then _
             ActiveWorkbook.Protect Structure:=False, Windows:=False
           If .ProtectContents Then _
            .Protect DrawingObjects:=False, Contents:=False, _
                     Scenarios:=False, userinterfaceonly:=False
    
    'You need to provide a value for xx
           For Each celCntr In .Range("B4:L25")
              celCntr = Trim(celCntr)
           Next celCntr
    
        If Not .ProtectContents Then _
          .Protect DrawingObjects:=True, Contents:=True, _
                   Scenarios:=True, userinterfaceonly:=True
        If Not ActiveWorkbook.ProtectStructure Then _
          ActiveWorkbook.Protect Structure:=True, Windows:=True
    
       End With
    
       Set wSheet = Nothing
    
    End Sub
    Note: I replaced the xx with an arbitrary value for the test you need to substitute your value.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Assuming you're not worried about formulas:

    Code:
    Sub TrimIt()
    with range("B4:L100")
       .value2 = Evaluate("INDEX(Trim(" & .Address(0,0) & "),,)")
    End With
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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