Text to column for all rows

[simonfonteyn]

Hi all,

I'm new with VB and tried for a long time to find the answer on different forums. Since I didn't find anything I decide to post it.
I would like to do a simple text to column. I have a lot of rows in column A. Problem is that with the following code the text to column only works for cell A1. I wish it could work for all the "non blank" cells of column A. This is the VBA code I have:

Private Sub CommandButton1_Click()
NewFN = Application.GetOpenFilename(FileFilter:="All Files (*.), *.", Title:="Please select a file")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If


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


End Sub

Thanks and regards,

Simon

[oeldere]

Use the built in function text to column

Excel 2007 => data => text to column

Excel 2007 (dutch) => gegevens => tekst naar kolommen

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[simonfonteyn]

Thank you!

This works perfectly...

I was doing it the way of oeldere but I wanted to make it easier with a VBA code.

Thanks again

[JOHN H. DAVIS]

You're welcome, glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.