Excel VBA Text to Columns

Posted on 2014-04-07
Medium Priority
Last Modified: 2014-04-07
I need to format multiple Columns A,D,I,L Text to Columns on all worksheets except for 2 (Invoice and Summary).

Text to Column Parameters:
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
 Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
  :=Array(1, 1), TrailingMinusNumbers:=True
Question by:shieldsco
LVL 39

Accepted Solution

nutsch earned 2000 total points
ID: 39983598
You can try this code, looping through the sheets and the columns:

Sub Macro1()
Dim shtLoop As Worksheet

For Each shtLoop In ActiveWorkbook.Worksheets

    If shtLoop.Name <> "Invoice" And shtLoop.Name <> "Summary" Then
           T2C shtLoop.[a1]
           T2C shtLoop.[D1]
           T2C shtLoop.[I1]
           T2C shtLoop.[L1]
    End If

Next shtLoop

End Sub

Private Sub T2C(rg As Range)

If Application.WorksheetFunction.CountA(rg.EntireColumn) Then _
    rg.EntireColumn.TextToColumns Destination:=rg, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
      :=Array(1, 1), TrailingMinusNumbers:=True
End Sub

Open in new window


Author Closing Comment

ID: 39983822
Works Good -- Thanks

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question