Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

Excel VBA Text to Columns

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
0
shieldsco
Asked:
shieldsco
1 Solution
 
nutschCommented:
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


Thomas
0
 
shieldscoAuthor Commented:
Works Good -- Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now