Macro for text to column

[Garrystone]

Hi Everyone,
I have the macro below that splits the cell content of column D.
However, this only works for those values in column D
(Destination:=Range("D1")). I have tried a few things but can't get it
right, I want the macro to run on any column I choose and not just
Column D. Can anybody help?

Sub Split_Cells()

ActiveCell.EntireColumn.Select
Selection.TextToColumns Destination:=Range("D1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1)),
TrailingMinusNumbers:=True
End Sub

Thank you

Garry

 

[FSt1]

hi
this is what is restricting it to D.
Destination:=Range("D1")
change to...
Destination:=selection.offset(0,1)

note: make sure that your column next to your target column is blank for
text to columns will overwrite data.

Regards
FSt1

 

[Dave Peterson]

Maybe something like:

Option Explicit
Sub Split_Cells()

Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection.Cells(1).EntireColumn, .UsedRange)
On Error Resume Next
End With

If myRng Is Nothing Then
MsgBox "Nothing to do!"
Exit Sub
End If

myRng.TextToColumns Destination:=myRng.Cells(1), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1)), _
TrailingMinusNumbers:=True

End Sub

 

[Dave Peterson]

I think it puts the parsed data back in column D--well, that's the way I read
the post.

If that's the case, then maybe:
Destination:=selection

 

[Garrystone]

Thanks FSt1
Worked fine, I just needed to alter the offset to 0,0 since in m
spreadsheet I am overwriting the initial column.

Thanks again

Garry