Text to Columns ? VBA

[zone709]

Hi I need to add more columns on this but it breaks. Doing something wrong or maybe I cant do it? and woul dhave to run this multiple times for different columns?

("D:D.U:U,T:T") -- I tried this but no luck

[FONT=Calibri][SIZE=3][COLOR=#000000]Sub Correction1() 'Text to Columns'[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]With Range("D:D")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    .TextToColumns[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    .NumberFormat ="General"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    ActiveSheet.Name ="Payroll Week Ending"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

​

 

[Joe4]

Text to Columns can only be applied to one column at a time. However, you can store all the columns you want to apply it to in an array and loop through that array, like this:

Sub MyTtoC()

    Dim cols As Variant
    Dim i As Long
    Dim c As String
    Dim rng As Range
    
    Application.ScreenUpdating = False
    
'   List columns you want to apply Text to Columns to
    cols = Array("D", "T", "U")
    
'   Loop through array
    For i = LBound(cols) To UBound(cols)
'       Get column letter
        c = cols(i)
'       Build range
        Set rng = Range(c & ":" & c)
'       Perform Text to Columns
        With rng
            .TextToColumns
            .NumberFormat = "General"
        End With
    Next i
    
'   Name sheet
    ActiveSheet.Name = "Payroll Week Ending"
    
    Application.ScreenUpdating = True
    
End Sub

So, to add/remove columns, simply update this line here:

    cols = Array("D", "T", "U")

 

[zone709]

k thanks I would think that it can be added to the range I did and be simple like that, but guess not been trying all morning thanks for the help like always

 

[Joe4]

You are welcome.

[FONT=Calibri][SIZE=3][COLOR=#000000]With Range[/COLOR][/SIZE][/FONT]("D:D,U:U,T:T")

would try to do them all at the same time, which will blow up Text to Columns.

You need to iterate your way through the columns. Using arrays is a good way to iterate through any list in VBA.

 

[zone709]

​gothca

 

[zone709]

HI i came back to this because i ran into an issue. For some reason column AD i received. There is data in this already do you wish to etccc.

Well by troubleshooting i found out why. When i click text to columns the dot is in Fixed width. When all other are in Delimited which work fine. Anything i can add into the doe to fixed this so its doesn't break checking AD.

 

[Joe4]

Try changing this line:

.TextToColumns

to this:

.TextToColumns DataType:=xlDelimited

 

[zone709]

it works thanks a lot. Sometimes i think why didn't i think of that. Maybe recording what i needed and take that piece lol. Thanks for the reply

 

[Joe4]

Maybe recording what i needed and take that piece lol

That is all I did here!

 

[KPM171]

This is fantastic, thank you Joe4!

Text to Columns can only be applied to one column at a time. However, you can store all the columns you want to apply it to in an array and loop through that array, like this:

Sub MyTtoC()

    Dim cols As Variant
    Dim i As Long
    Dim c As String
    Dim rng As Range
    
    Application.ScreenUpdating = False
    
'   List columns you want to apply Text to Columns to
    cols = Array("D", "T", "U")
    
'   Loop through array
    For i = LBound(cols) To UBound(cols)
'       Get column letter
        c = cols(i)
'       Build range
        Set rng = Range(c & ":" & c)
'       Perform Text to Columns
        With rng
            .TextToColumns
            .NumberFormat = "General"
        End With
    Next i
    
'   Name sheet
    ActiveSheet.Name = "Payroll Week Ending"
    
    Application.ScreenUpdating = True
    
End Sub

So, to add/remove columns, simply update this line here:

    cols = Array("D", "T", "U")