Q:

Convert to number error in excel after copy paste

I am facing issue after copy and paste from export file from SAP it ask to convert to number option which is like error without converting to number formulas do not work. also cell value is number only.
I tried to make macro but it works on single column only. how to make it work on multiple column together and make it faster also as it stuck exel for long time.
code for converting to number
Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

Columns("F:F").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

Columns("G:G").Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

How shorter code and combine for multiple columns in single line.

A:

Use an xlFixedWidth in your Range.TextToColumns method.
Dim c As Long, vCOLs As Variant
vCOLs = Array(1, 6, 7)  'columns A, F and G

With Worksheets("Sheet1")
    For c = LBound(vCOLs) To UBound(vCOLs)
        With .Column(c)
            .TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, _
                           FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
        End With
    Next c
End With

The variant array allow you to quickly specify the columns to be processed. Even with a large (~250K) number of rows, this should be fairly quick to cycle through.

