2

I have some VBA code in a macro that was working fine in Excel 2016 with update 1703. However, one user just had the new update (1705) and now this macro gives a "1004 application/object unknown" error. The error happens in this section of code, specifically at the second Selection.Offset(1,0).Select line.

'Get the values from the first column
Range("A2 : A" & NumRows).Select
Selection.Copy
'Paste the copy once for each column containing data
For I = 2 To NumCols
    Range("A2").Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    ActiveSheet.Paste
Next
'Insert a new column at the front of the worksheet
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
'Get the first data column header and copy it
Range("C1").Select
Selection.Copy
'Paste the selection into the first column
Range("A2:A" & NumRows).Select
ActiveSheet.Paste
'Get and copy the rest of the headers
For I = 1 To NumCols
    Range("C1").Select
    Selection.Offset(0, I).Select
    Selection.Copy
    For C = 2 To NumRows
        Range("A2").Select
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
        ActiveSheet.Paste
    Next
Next

One thing I noticed between computers with 1703 and 1705 is that on 1703 the new column inserted is empty, and with 1705 the new column has values all the way to the maximum number of rows (the row number is > 1 million).

So I guess my question is, how can I insert the new column without the data filling up all rows until the Excel maximum row?

Community
  • 1
  • 1
scratt
  • 190
  • 9
  • First things First. See [THIS](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) . Amend the code and then let us know if you are still facing the problem – Siddharth Rout Jun 09 '17 at 16:10
  • The code was working before the update, and was recorded via the "record macros" feature in Excel. The post you linked seemed to indicate that it would improve the performance. So thanks for trying to help me improve my performance, but that's really not what I am trying to do with this question. – scratt Jun 09 '17 at 16:29
  • Seems like you missed my answer in that post :) `.Select` is highly unreliable and can cause Runtime errors :) – Siddharth Rout Jun 09 '17 at 16:43

1 Answers1

3

I did some research on this. Knowing that it worked in 1703 and didn't work in 1705, I looked for a changelog for the different versions. This is all I could find:

https://support.office.com/en-us/article/What-s-new-in-Excel-2016-for-Windows-5fdb9208-ff33-45b6-9e08-1f5cdb3a6c73#Audience=Office_Insiders

It says they added a new feature called "Keep the Copy" that "With this update you can copy your cells, and before you paste, you can still do other tasks like typing or inserting cells." The VBA code was set up to select a column and then insert a new (empty) column in front of it. However, because cells had previously been copied, the new feature kept them and inserted them into the new column, filling the entire column with values (over a million rows).

So since I noticed the old version didn't have values in the newly-created column, I thought the "Keep the Copy" functionality might be the culprit. I searched Stack Overflow for a way to clear the contents (Thanks SO!) of the clipboard before creating the new column.

I changed my code from this:

    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight

To this:

Application.CutCopyMode = False 'Clears any copied cells so they do not get inserted into the new column
Columns("A:A").Select
Selection.Insert Shift:=xlToRight

and that fixed the issue. I hope someone else finds value in this knowledge.

scratt
  • 190
  • 9
  • 1
    Thank you so much! This was driving me nuts, totally broke functionality of an old program I help maintain that works fine in all previous versions of Excel. We were starting to think it was a bug. Seriously a dick move from Microsoft to add this and have it default to turned on. – WebChemist Sep 19 '17 at 09:44