Excel 2003 vs Excel 2007?

[Enz]

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 or suggestions on what to look at.


regards,
Enzo

 

[Charlie]

Is it possible 2007 can handle more rows? If so then 1048576 is probably
correct. My version displays 65536. In any event, how did you dim llastRow?

 

[Enz]

Is it possible 2007 can handle more rows?  If so then 1048576 is probably
correct.  My version displays 65536.  In any event, how did you dim llastRow?










- Show quoted text -

Thanks Charlie. I noticed that for whatever reason, I have lost the
original DIM. I have added it back as Long and it still did not
work. But I just tried something else. I noticed that when you hover
over the 'Rows.count' in the Excel 2007 version it was still
displaying 1048576 which I know was not accurate. So I changed
'Rows.Count' so the statement now reads

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

This now works. Which points to an oddity of Excel 2007 that it
requires you to fully qualify your Rows.Count (because the Excel 2003
did not and it still worked) or am I missing something?

llastRow = wsEmployeeCap.Cells(wsEmployeeCap.Rows.Count,
1).End(xlUp).Row

 

[Jon Peltier]

The point is, you should be completely referencing Rows.Count in Excel 2003.
The problem is more likely on the part of Excel 2003 for not requiring it.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Is it possible 2007 can handle more rows? If so then 1048576 is probably
correct. My version displays 65536. In any event, how did you dim
llastRow?










- Show quoted text -

Thanks Charlie. I noticed that for whatever reason, I have lost the
original DIM. I have added it back as Long and it still did not
work. But I just tried something else. I noticed that when you hover
over the 'Rows.count' in the Excel 2007 version it was still
displaying 1048576 which I know was not accurate. So I changed
'Rows.Count' so the statement now reads

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

This now works. Which points to an oddity of Excel 2007 that it
requires you to fully qualify your Rows.Count (because the Excel 2003
did not and it still worked) or am I missing something?

llastRow = wsEmployeeCap.Cells(wsEmployeeCap.Rows.Count,
1).End(xlUp).Row