Autofit after macro to split text

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hi all,


    I have a macro that is used to split text in one cell into separate columns with a space delimiter. I want the macro to also autofit the columns after they are split, but the Call function I'm using gives me this error:


    Compile Error:
    Argument not optional


    Here is the code I am using:

    Code
    Sub TextToCols(control As IRibbonControl)
    Worksheets("Sheet1").Activate
    Selection.TextToColumns DataType:=xlDelimited, _
     ConsecutiveDelimiter:=True, Space:=True
     Call Autofit_Columns
    End Sub


    And yes, There is a macro called Autofit_Columns in the worksheet:

    Code
    Sub Autofit_Columns
    Dim sht As Worksheet
    'AutoFit All Columns on Worksheet
        Columns.AutoFit
    End Sub


    It's not a big deal to run the macros seperately, but I'd like to minimize clicking.


    Thanks for any help.
    Robert

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!