Excel 2003 VBA guru please!

[WhiteD3]

This is a problem that happens on a couple of excel tools we use. Tried before to fix it but have never found a satisfactory, long term fix.

For example:

Excel 2003 .xls file where 340 rows and 12 columns are used, 1.6MB in size. Basically its a list of job numbers, names, order numbers, etc. It has a number of VBA subs to hide rows, sort, add and delete rows.

When the file gets to ~340 rows and we try to add a row using the macro below, Excel locks up. Eventually (after 10 minutes or so) it will come up with the following:

"Excel cannot complete this task with available resources......:

and then
"Run time error '-2147417848 (800 10 108)':
Automation error
The object invoked has disconnected from its clients"

This occurs on multiple PCs running Vista business and Win 7, versions of Excel from 2003 to current.

Sub InsertRow()
'Inserts a row at the bottom of the sheet.
ActiveSheet.Unprotect
n = Range("EndRow").Row 'Find the row number of the row named EndRow.
ActiveSheet.Range(Cells(n - 1, 1), Cells(n - 1, 19)).Copy 'Copy the last row.
ActiveSheet.Range(Cells(n, 1), Cells(n, 12)).Insert Shift:=xlShiftDown 'Insert
ActiveSheet.Paste Range(Cells(n, 1), Cells(n, 19)) 'Paste
ActiveSheet.Range(Cells(n, 1), Cells(n, 12)).ClearContents 'Clear the data in the new row.
Cells(n, 1).Activate
ActiveSheet.Protect
End Sub

Any and all help would be appreciated.

[mikehzz]

Mate there is nothing wrong with that code. I have run it to automatically insert 2000 rows in a sheet I just set up with no issues. I would suggest that there is a problem somewhere in your sheet. Does your sheet only have 340 rows or are there many more but hidden? The error type smacks of some sort of memory or data corruption. Cheers

[mikehzz]

Also, is there any reason you are copying, pasting and clearing? Removing those lines might bypass the error if it is data related.

[Ferret]

The code you posted seems to serve only to move the address of range 'End Row' down by 1 row.

For example if the named range 'End Row' was initially located on row 17 then your code performs the following steps

1 Copy to clipboard range (A16:S16)
2 Inset new row at range (A17:L17) and move existing cells down
3 Paste clipboard (ie data from range A16:S16) to range (A17:S17)
4 Clear range (A17:L17)
5 Activate cell (A17)

The net effect is:

1 The data on row 16 is unchanged
2 You now have an empty row on row 17
3 The named range 'End Row' is now on row 18.

This could be all achieved a little more simply. It looks like your 'copy' and 'paste' steps are redundant. You immediately clear what you copied.The only other comment I would make is change all instances of 'ActiveSheet' to refer specifically to the sheet in question. ie

Worksheets("Sheet1").Range(Cells(n - 1, 1), Cells(n - 1, 19)).Copy

instead of

ActiveSheet.Range(Cells(n - 1, 1), Cells(n - 1, 19)).Copy

Making a call to 'ActiveSheet' can cause confusion if for whatever reason the 'active sheet' ceases to be the 'active sheet'. This is something like making an 'unqualified call'. VBA does not specifically know which 'instance' of a sheet is now the 'active sheet'.

All I can think of.

[WhiteD3]

Also, is there any reason you are copying, pasting and clearing? Removing those lines might bypass the error if it is data related.

Only that by doing this I can create a new row with the same format.

[WhiteD3]

The code you posted seems to serve only to move the address of range 'End Row' down by 1 row.

For example if the named range 'End Row' was initially located on row 17 then your code performs the following steps

1 Copy to clipboard range (A16:S16)
2 Inset new row at range (A17:L17) and move existing cells down
3 Paste clipboard (ie data from range A16:S16) to range (A17:S17)
4 Clear range (A17:L17)
5 Activate cell (A17)

The net effect is:

1 The data on row 16 is unchanged
2 You now have an empty row on row 17
3 The named range 'End Row' is now on row 18.

This could be all achieved a little more simply. It looks like your 'copy' and 'paste' steps are redundant. You immediately clear what you copied.The only other comment I would make is change all instances of 'ActiveSheet' to refer specifically to the sheet in question. ie

Worksheets("Sheet1").Range(Cells(n - 1, 1), Cells(n - 1, 19)).Copy

instead of

ActiveSheet.Range(Cells(n - 1, 1), Cells(n - 1, 19)).Copy

Making a call to 'ActiveSheet' can cause confusion if for whatever reason the 'active sheet' ceases to be the 'active sheet'. This is something like making an 'unqualified call'. VBA does not specifically know which 'instance' of a sheet is now the 'active sheet'.

All I can think of.

Thanks. Basically all I'm doing is finding the last row, copy, insert it and clear it to create a new row with the same format.

I'll change the "activesheet" references.

Thanks.

[mikehzz]

Are any of the cells being copied containing formulae or links to cells elsewhere in the workbook perhaps on other sheets? If the links are somehow broken on some of them it may cause a problem.

[WhiteD3]

Mate there is nothing wrong with that code. I have run it to automatically insert 2000 rows in a sheet I just set up with no issues. I would suggest that there is a problem somewhere in your sheet. Does your sheet only have 340 rows or are there many more but hidden? The error type smacks of some sort of memory or data corruption. Cheers

There's 340 formated rows at the moment. InsertRow just makes it 341, etc.

No hidden rows and the sheet the standard 65,000 blank rows.

[WhiteD3]

Are any of the cells being copied containing formulae or links to cells elsewhere in the workbook perhaps on other sheets? If the links are somehow broken on some of them it may cause a problem.

No links, no formulae.

[Ferret]

No links, no formulae.

Any 'events'

[WhiteD3]

Mate there is nothing wrong with that code. I have run it to automatically insert 2000 rows in a sheet I just set up with no issues. I would suggest that there is a problem somewhere in your sheet. Does your sheet only have 340 rows or are there many more but hidden? The error type smacks of some sort of memory or data corruption. Cheers

Corrupted! Thanks for the clue :D

The formatting of the cells (single lines around each cell) was corrupted. I created a new sheet and when I tried to copy/paste special the formatting Excel crashed. So I recreated the formatting and the size of the file overall went from 1.4GB to 143KB.

And it works!

Thanks guys.