Need to change Range("A1") to refer to row 1 of active cell's column

[General Ledger]

Dear All,

I am trying to create a Macro which uses the Text to Columns feature of Excel. My problem is telling the macro the Destination of the output.


When I execute the macro, I want it to:
1. Select the entire column of the active cell
2. Change the format of all the cells to Text
3. Place the output on top of the current column
4. End

Here is what I have so far:

Sub TextToColumns()
'
Application.ScreenUpdating = False
'
ActiveCell.EntireColumn.TextToColumns Destination:=Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
End Sub


My problem is with Destination:=Range("a1"). I want the Destination to start in row 1 of the column of the active cell. The way the macro works now, the output is placed beginning at cell A1 of the worksheet.

Thanks,

GL

 

[njimack]

Change Range("a1") to Cells(1,ActiveCell.Column)

 

[General Ledger]

Neil,

PERFECT!

Thank you,

GL