locked
Excel Macro - Define Range as the cell in which the Macro is executed RRS feed

  • Question

  • I have 4000 rows of data where 1 column has multiple lines of text that I am trying to use the Text to Columns tool to transform.  I created a macro that can do the function however I can't figure out how to define the Range as the cell in which I execute the macro.  Sample code:

    Sub ExampleSplit1()
        Selection.TextToColumns _
          Destination:=Range("A2"), _
          DataType:=xlDelimited, _
          TextQualifier:=xlDoubleQuote, _
          ConsecutiveDelimiter:=False, _
          Tab:=True, _
          Semicolon:=False, _
          Comma:=False, _
          Space:=False, _
          Other:=True, _
          OtherChar:="-"
    End Sub

    If the shortcut key to execute the macro is defined as Ctrl + Shift + Q and I perform this in Cell D15 I want the range to be defined as D15.  Can't figure out how to do this.  Thanks Brian

    Friday, July 10, 2020 3:16 PM

All replies

  • Simply omit the Destination argument:

    Sub ExampleSplit1()
        Selection.TextToColumns _
          DataType:=xlDelimited, _
          TextQualifier:=xlDoubleQuote, _
          ConsecutiveDelimiter:=False, _
          Tab:=True, _
          Semicolon:=False, _
          Comma:=False, _
          Space:=False, _
          Other:=True, _
          OtherChar:="-"
    End Sub


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

    Friday, July 10, 2020 3:24 PM