Delimited macro with a variable column

**alonso1111** · 06-11-2013, 11:56 PM

Hi all, I want to do a text to column, then delimit on a column of my data. I could probably do it if its a fixed column but what I really want to do is to have the column that is to be delimited to be variable,ie, i could choose column A or B or C etc to do the macro. I paste whatever i have as of now below.Thanks for any help! I tried to have a variable called "col",but i am not able to integrate it into this macro. Also, for the Destination, is col.select correct? Thanks again for the help!

Sub ExampleSplit1()
Dim col as Range

    Selection.TextToColumns _
      Destination:=col.select, _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=False, _
      Semicolon:=False, _
      Comma:=False, _
      Space:=False, _
      Other:=True, _
      OtherChar:="*"

End Sub

**Mordred** · 06-12-2013, 12:05 AM

Hi alonso1111, you have to assign a range to col first. For instance, to assign a range or cell you would have something like

Sub ExampleRange()

    Dim col As Range
    Dim ws1 As Worksheet
    
    Set ws1 = Worksheets("Sheet1")
    Set col = ws1.Range("A1:A20")
    'Do something with the range col here'
    
End Sub

Now, for your particular problem, I'm not sure how to apply this because I don't know what the "Selection" would be. Perhaps you could upload a mock workbook with some mock data in it with a before and after scenario as well.

**alonso1111** · 06-12-2013, 01:27 AM

umm.what i meant was if i can make the range of the col as variable. with regards to what u posted,u let range of col be A1:A20.So it would be fixed as that right? i was thinking if its possible to make the range of the col variable, that is i can select which column of data to do the delimited macro

**Mordred** · 06-12-2013, 07:57 AM

I'm going to say yes but you'd probably have to use the Worksheet_Change event if you want col to be dynamic. I can either show you a generic version of this event or you can upload a mock workbook with non-sensitive or private data with a before and after type scenario. That would be preferable and would reduce the guess work from me and others on the forum.