Text to Columns overwrites columns

Question

I got another Problem with VBA. I used the TexttoColumns Sub to separate every Cell in my Column by semicolon. Now I wanted to insert Columns after that one, which should contain the separated values. It all worked pretty well in the beginning, but now it suddenly won't insert new cells, but overwrite the old ones.

Example (Wish) :

Row1           Row2   Row3   
Tree;PC;House |Data1 |Data2 -->  Tree|PC|House|Data1|Data2

Example(How it is):

Tree;PC;House|Data1|Data2 --> Tree|PC|House

Workbooks(Ziel).Worksheets(Zieltab).Columns(Spalte).TextToColumns Destination:=Columns(Spalte), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 2), Array(2, 2))

as far is i know it, it will allways overwrite.. you may use =A1&";"&B1&";"&C1... after the last column and drag it down... copying it (value only) to the first column... then again use TextToColumns — Dirk Reichel
– Dirk Reichel, Commented Nov 6, 2015 at 9:42

user4039065 · Accepted Answer · 2015-11-06 09:50:48Z

2

A Range.TextToColumns method does not insert columns. It will always overwrite data if allowed to proceed.

With Workbooks(Ziel).Worksheets(Zieltab).Columns(Spalte)
    'insert two columns to the right
    .Cells(1).Resize(1, 2).Offset(0, 1).EntireColumn.Insert
    'split the first column into itself and the two new column
    .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))
End With

answered Nov 6, 2015 at 9:50

user4039065

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Text to Columns overwrites columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related