Excel 2007 potential issue?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Enz - 21 Jan 2008 15:17 GMT

I have developed a macro on a PC with Excel 2003 version that works
fine. I also tested the same macro on another colleague's PC also
running Excel 2003 and there are no issues. I sent the macro to a
colleague that is running Excel 2007, and when he runs the macro it
abends with a Run-time error '1004'. The exact line of code that
fails is as follows:

llastRow = wsEmployeeCap.Cells(Rows.Count, 1).End(xlUp).Row
'calculates based on column 1

In debug mode in the Excel 2007 version, 1048576 is displayed when
you hover over the 'Rows.Count' when using an input file. When I run
the same statement for the same input file, on my machine running
Excel 2003, the 'Rows.Count' displays accurately at 42330 rows.

My question, is there a known bug with Excel 2007 for this? Or
perhaps there is an issue with my statement?

Thanks for any help.

regards,
Enzo

Reply to this Message

Bernard Liengme - 21 Jan 2008 15:37 GMT

I put this in the immediate window
msgbox(sheet1.Cells(Rows.Count, 1).End(xlUp).Row)
and in XL2007 it gave me the correct answer

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

>I have developed a macro on a PC with Excel 2003 version that works
> fine. I also tested the same macro on another colleague's PC also
[quoted text clipped - 18 lines]
> regards,
> Enzo

Reply to this Message

Tyro - 21 Jan 2008 21:04 GMT

In Excel 2007, the property rows.count returns 1048576, which is correct as
Excel 2007 has 1,048,576 rows. In Excel 2003, rows.count should return
65536.
In Excel 2007, the property columns.count returns 16384 in Excel 2003 it
returns 256

Tyro

>I have developed a macro on a PC with Excel 2003 version that works
> fine. I also tested the same macro on another colleague's PC also
[quoted text clipped - 18 lines]
> regards,
> Enzo

Reply to this Message