none
Remove internal space from descriptions across multiple sheets. RRS feed

  • Question

  • I have several hundred sheets over 16 budget models that contain a space that I need to remove globally.

    Column A of each sheet between control sheets START and END (both of which are empty) Have descriptions that contain an added space that need to removed to match our Oracle Chart of Accounts.

    The range is A4 to A56 on each of the sheets between START and END and all start with six characters followed by a space.  It's this first space that we want to eliminate.

    Example of Description: AA4100 - Food Supplies. The Space is after "AA4100" and before "- Food Supplies"

    Since I have so many budget sheets (around 500) I would prefer a VBA solution to using Grouped Sheets and copy and pasting the correct descriptions manually.

    Hopefully all of that makes sense. Please let me know if it's not.

    Cheers

    Jim

    Monday, June 25, 2018 7:43 AM

Answers

  • If there could be other double spaces that you want to preserve:

    Sub RemoveSpace()
        Dim w As Worksheet
        Dim i As Long
        Dim r As Long
        On Error GoTo ErrHandler
        Application.Cursor = xlWait
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        For i = Worksheets("START").Index + 1 To Worksheets("END").Index - 1
            Set w = Worksheets(i)
            For r = 4 To 56
                With w.Range("A" & r)
                    .Value = Left(.Value, 6) & Mid(.Value, 8)
                End With
            Next r
        Next i
    ExitHandler:
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        Application.Cursor = xlDefault
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub

    If there aren't, this version should be slightly more efficient:

    Sub RemoveSpace()
        Dim w As Worksheet
        Dim i As Long
        On Error GoTo ErrHandler
        Application.Cursor = xlWait
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        For i = Worksheets("START").Index + 1 To Worksheets("END").Index - 1
            Set w = Worksheets(i)
            w.Range("A4:A56").Replace What:=Space(2), Replacement:=Space(1), LookAt:=xlPart
        Next i
    ExitHandler:
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        Application.Cursor = xlDefault
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Jim from Oz Tuesday, June 26, 2018 11:25 PM
    Monday, June 25, 2018 8:42 AM

All replies

  • If there could be other double spaces that you want to preserve:

    Sub RemoveSpace()
        Dim w As Worksheet
        Dim i As Long
        Dim r As Long
        On Error GoTo ErrHandler
        Application.Cursor = xlWait
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        For i = Worksheets("START").Index + 1 To Worksheets("END").Index - 1
            Set w = Worksheets(i)
            For r = 4 To 56
                With w.Range("A" & r)
                    .Value = Left(.Value, 6) & Mid(.Value, 8)
                End With
            Next r
        Next i
    ExitHandler:
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        Application.Cursor = xlDefault
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub

    If there aren't, this version should be slightly more efficient:

    Sub RemoveSpace()
        Dim w As Worksheet
        Dim i As Long
        On Error GoTo ErrHandler
        Application.Cursor = xlWait
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
        For i = Worksheets("START").Index + 1 To Worksheets("END").Index - 1
            Set w = Worksheets(i)
            w.Range("A4:A56").Replace What:=Space(2), Replacement:=Space(1), LookAt:=xlPart
        Next i
    ExitHandler:
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        Application.Cursor = xlDefault
        Exit Sub
    ErrHandler:
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Jim from Oz Tuesday, June 26, 2018 11:25 PM
    Monday, June 25, 2018 8:42 AM
  • Thanks Hans,

    With only the active workbook open nothing seemed to happen.  Either code didn't remove the first space in the range or any other spaces that I can see.

    Cheers

    Jim

    Monday, June 25, 2018 11:19 PM
  • Are you sure that the START sheet is to the left of the END sheet? The code worked in a small sample workbook that I created for the purpose.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, June 26, 2018 7:21 AM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Tuesday, June 26, 2018 7:34 AM
  • Hi Hans,

    Strangely enough both worked after I copied it into a fresh workbook...sometimes Excel baffles me...most of the time in a good way ☺

    When the code runs it removes the first space and when run again it progressively removed each succeeding character. I can live with this but Is there a way to have it stop after the target character has been removed? Just think of other users accidentally running it more than once.

    Cheer and thanks

    Jim

    Tuesday, June 26, 2018 11:24 PM
  • The second version shouldn't remove extra characters when run a second time.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 27, 2018 7:04 AM