Text To Columns macro in Excel 2010

[metsmaniac]

I want to create a "Text_To_Columns" macro in Excel 2010 that is not restricted to the range within the macro. The purpose of the macro is to be able to select a range of cells that are formatted as text and convert the text to values, so I want to select the range and then run the macro.

The macro below was created by turning on Record Macro, clicking Data=>Text to Columns=>Finish, then turning off the macro. As you can see, it's restricted to range "K36".

Is there a way to modify the macro below to do the above? Thanks in advance.

Sub Text_To_Columns()

' Text_To_Columns Macro

Selection.TextToColumns Destination:=Range("K36"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

End Sub

 

[CharlesChuckieCharles]

Not sure why you'd bother writing a macro for use in a way that using the built in function would be about the same effort?

formatted as text and convert the text to values did you columns when you said Values

 

[metsmaniac]

Speed & efficiency - the essence of macros. 1 click vs. 3, assuming I'm not in the Data menu.

 

[CharlesChuckieCharles]

use this bit
Destination:=Range(ActiveCell.Address)

when you select a range of Cells the ActiveCell is the most Top left hand corner of the selection

 

[metsmaniac]

That worked. Thanks very much.