VBA from 2010 to 2016: copy/paste text as undelimited, even though it contains delimitation characters

Question

I have converted a macro enabled document I have from XLSM to an XLSB as this was created in Excel 2010 but users have now moved to Excel 2016.

I have come across an issue when I copy information containing delimitation characters from a Notepad e.g. text; 1; 2; 3 and paste this into the Excel: it delimits itself into multiple columns but I need it to stay in the un-delimited format.

Is there anyway to go about this? the only way so far I have found to do this is to copy it to another Excel sheet first but this isn't practical.

Selection.TextToColumns Destination:=Range("C2"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
    1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
    , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
    Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
    25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1)), TrailingMinusNumbers _
    :=True

Columns("C:AD").Select
Columns("C:AD").EntireColumn.AutoFit
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Columns("E:E").Cut Destination:=Columns("H:H")
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("J:J").Select
Range("J:J,L:Y,AA:AB").Select
Range("AA1").Activate
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 1
Range("A1").Select

It is not clear why a move from Excel 2010 to Excel 2016 would require changing the format from xlsm to xlsb, but otherwise, yes, it is an annoying feature of Excel that it remembers once done text-to-column operation on a sheet and will continue doing it automatically on that sheet. This behaviour is pretty old though, but you can [work around it](https://superuser.com/a/992863/52365). — GSerg, May 29 '18 at 08:03
When you use Text to Columns manually, the settings GPS you use stick, including for Copy Paste. Try running a dummy Text to Columns with all delimiters turned off, then try again — chris neilsen, May 29 '18 at 09:01
@SolarMike I will edit my question to include the code, I would be quite convinced removing the array could fix this issue but the problem depends on how the user pastes the information into the excel sheet. The first time it will paste In how I need it to e.g. text; 1; 2; 3 and other times it will paste into multiple columns, however it seems to be after I run the macro the first time this happens. So the sheet appears to hold the formatting created via the array. — Paula, May 29 '18 at 09:33
@Paula The issue is not coming from your macro. It is because Excel remembers the last text-to-column options. • Additionally I recommend to read and follow: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, May 29 '18 at 09:35
@chrisneilsen I have given this a go but it doesn't stop the information from splitting — Paula, May 29 '18 at 09:42

Pᴇʜ · Accepted Answer · 2018-05-29T10:55:54.313

You could fake a the text to column to clear its options

Sub ClearTextToColumns()
    If IsEmpty(Range("A1")) Then Range("A1") = "XXXX"
    Range("A1").TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        OtherChar:=""
    If Range("A1") = "XXXX" Then Range("A1") = ""
End Sub

Note that the contents of cell A1 will not be modified because no operations are specified for the TextToColumns method.

Note that the second part of your code can be reduced to

Columns("C:AD").EntireColumn.AutoFit
Columns("G:G").Delete Shift:=xlToLeft
Columns("E:E").Cut Destination:=Columns("H:H")
Columns("E:E").Delete Shift:=xlToLeft
Range("J:J,L:Y,AA:AB").Delete Shift:=xlToLeft

Using .Select is a bad practice and not necessary to do these actions. If you avoid that your code gets much more stable, faster and prevents many issues.
Also see: How to avoid using Select in Excel VBA.

@PEH this seems to have done the trick, I have built this into the clear down function and tested a number of times and all information seems to be pulling correctly, thank you — Paula, May 29 '18 at 09:50
@Paula see my edit, for an improvement of your code. Note that the macro recorder uses a lot of `.Select` but you should remove them where ever possible. — Pᴇʜ, May 29 '18 at 10:56
@PEH thank you, this does seem to speed it all up. I will give it a go removing the .select where possible — Paula, May 29 '18 at 12:31

VBA from 2010 to 2016: copy/paste text as undelimited, even though it contains delimitation characters

1 Answers1