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,952
    Thanks
    422
    Thanked 1,605 Times in 1,449 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,980
    Thanks
    157
    Thanked 773 Times in 705 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
    3,412
    Thanks
    164
    Thanked 641 Times in 609 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 16:15.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,952
    Thanks
    422
    Thanked 1,605 Times in 1,449 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,349
    Thanks
    4
    Thanked 228 Times in 210 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
  •