Thread: Excel 2003 VBA guru please!

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

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

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.

Originally Posted by mikehzz

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.

Originally Posted by 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.

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.

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.

Originally Posted by 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

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.

Originally Posted by 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.

No links, no formulae.