never really used 2007, so i'm wondering if you've seen this behavior in
2010, or 2007, for that matter.

typically, in 2003, ws3.Cells(Rows.Count, "A").End(xlUp).Row, would return
the last row

but, in 2010, i frequently get debugs on this line when opening a 2003
workbook in compatibility mode.

so, i've resorted to change the code to this and it seems to resolve the
issue:
ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row

seems excel is sometimes thinking it has a million rows, when in fact there
are only 65000, so it debugs. seems to happen when i open another workbook
with code.

anyone seen this or have a better way?


--


Gary Keramidas
Excel 2003

Hello:

If you want to find the last row, I would recommend using the process
described in John Walkenbach's book, Excel 2003 Power Programming with VBA...
it's the same for 2007.

He recommends this process to find the last active row number:

intNumberOfRowsInWorksheet = ActiveSheet.UsedRange.Rows.Count
intNumberOfRowsInWorksheet = intNumberOfRowsInWorksheet +
ActiveSheet.UsedRange.Row - 1

This combination of statements is necessary in case the data starts in other
than row 1. Try it for yourself.... it's the Industry Standard.

--
Rich Locus
Logicwurks, LLC


"Gary Keramidas" wrote:

never really used 2007, so i'm wondering if you've seen this behavior in
2010, or 2007, for that matter.

typically, in 2003, ws3.Cells(Rows.Count, "A").End(xlUp).Row, would return
the last row

but, in 2010, i frequently get debugs on this line when opening a 2003
workbook in compatibility mode.

so, i've resorted to change the code to this and it seems to resolve the
issue:
ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row

seems excel is sometimes thinking it has a million rows, when in fact there
are only 65000, so it debugs. seems to happen when i open another workbook
with code.

anyone seen this or have a better way?


--


Gary Keramidas
Excel 2003


.

Hello:

I would use what I would consider the "Industry Standard". It is in John
Walkenbach's book on VBA Excel Programming:

Public Sub Tester()
Dim intNumberOfRowsInWorksheet As Long
intNumberOfRowsInWorksheet = ActiveSheet.UsedRange.Rows.Count
intNumberOfRowsInWorksheet = intNumberOfRowsInWorksheet +
ActiveSheet.UsedRange.Row - 1
MsgBox ("Number Of Rows in Worksheet = " & intNumberOfRowsInWorksheet)
End Sub

Hope that helps.
--
Rich Locus
Logicwurks, LLC


"Gary Keramidas" wrote:

never really used 2007, so i'm wondering if you've seen this behavior in
2010, or 2007, for that matter.

typically, in 2003, ws3.Cells(Rows.Count, "A").End(xlUp).Row, would return
the last row

but, in 2010, i frequently get debugs on this line when opening a 2003
workbook in compatibility mode.

so, i've resorted to change the code to this and it seems to resolve the
issue:
ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row

seems excel is sometimes thinking it has a million rows, when in fact there
are only 65000, so it debugs. seems to happen when i open another workbook
with code.

anyone seen this or have a better way?


--


Gary Keramidas
Excel 2003


.

i've seen usedrange be problematic at times. some cleaning of the worksheet
was necessary to get that function to work.

--


Gary Keramidas
Excel 2003


"Rich Locus" wrote in message
...
Hello:

I would use what I would consider the "Industry Standard". It is in John
Walkenbach's book on VBA Excel Programming:

Public Sub Tester()
Dim intNumberOfRowsInWorksheet As Long
intNumberOfRowsInWorksheet = ActiveSheet.UsedRange.Rows.Count
intNumberOfRowsInWorksheet = intNumberOfRowsInWorksheet +
ActiveSheet.UsedRange.Row - 1
MsgBox ("Number Of Rows in Worksheet = " & intNumberOfRowsInWorksheet)
End Sub

Hope that helps.
--
Rich Locus
Logicwurks, LLC


"Gary Keramidas" wrote:

never really used 2007, so i'm wondering if you've seen this behavior in
2010, or 2007, for that matter.

typically, in 2003, ws3.Cells(Rows.Count, "A").End(xlUp).Row, would
return
the last row

but, in 2010, i frequently get debugs on this line when opening a 2003
workbook in compatibility mode.

so, i've resorted to change the code to this and it seems to resolve the
issue:
ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row

seems excel is sometimes thinking it has a million rows, when in fact
there
are only 65000, so it debugs. seems to happen when i open another
workbook
with code.

anyone seen this or have a better way?


--


Gary Keramidas
Excel 2003


.

I am using another way to determining the Last row in excel worksheet as:

lastrow=Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

actually I used same think which you are using for determining the last row but I want to take a last row after row 10 which may be not used in some workbook so I am adding some value in 10th row of one cell and then using the last row. It is working fine.



Gary Keramidas wrote:

excel 2010 question regarding determining the last row
11-Mar-10

never really used 2007, so i'm wondering if you have seen this behavior in
2010, or 2007, for that matter.

typically, in 2003, ws3.Cells(Rows.Count, "A").End(xlUp).Row, would return
the last row

but, in 2010, i frequently get debugs on this line when opening a 2003
workbook in compatibility mode.

so, i've resorted to change the code to this and it seems to resolve the
issue:
ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row

seems excel is sometimes thinking it has a million rows, when in fact there
are only 65000, so it debugs. seems to happen when i open another workbook
with code.

anyone seen this or have a better way?


--


Gary Keramidas
Excel 2003

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
What's New for Developers in SharePoint 2010 Object Model?
http://www.eggheadcafe.com/tutorials...evelopers.aspx

Hi Gary,

Here is one possible explanation:
Its possible to have both a compatibility workbook (65K rows) and a
non-compatibility workbook (million rows) open at the same time.

If you dont fully qualify the row count (as in ws3.Cells(Rows.Count,
"A") then its possible that the active sheet referred to by Rows.count
will have a million rows but that ws3 will only have 65K.

I was caught by this kind of problem in 2007 and spent some time
puzzling about it before I realised what was happening.

regards
Charles

never really used 2007, so i'm wondering if you've seen this behavior in
2010, or 2007, for that matter.

typically, in 2003, ws3.Cells(Rows.Count, "A").End(xlUp).Row, would return
the last row

but, in 2010, i frequently get debugs on this line when opening a 2003
workbook in compatibility mode.

so, i've resorted to change the code to this and it seems to resolve the
issue:
ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row

seems excel is sometimes thinking it has a million rows, when in fact there
are only 65000, so it debugs. seems to happen when i open another workbook
with code.

anyone seen this or have a better way?

yes, charles, that's exactly what i saw happening and discovered this was a
way of getting it to work correctly.

--


Gary Keramidas
Excel 2003


"Charles Williams" wrote in message
...
Hi Gary,

Here is one possible explanation:
Its possible to have both a compatibility workbook (65K rows) and a
non-compatibility workbook (million rows) open at the same time.

If you dont fully qualify the row count (as in ws3.Cells(Rows.Count,
"A") then its possible that the active sheet referred to by Rows.count
will have a million rows but that ws3 will only have 65K.

I was caught by this kind of problem in 2007 and spent some time
puzzling about it before I realised what was happening.

regards
Charles

never really used 2007, so i'm wondering if you've seen this behavior in
2010, or 2007, for that matter.

typically, in 2003, ws3.Cells(Rows.Count, "A").End(xlUp).Row, would return
the last row

but, in 2010, i frequently get debugs on this line when opening a 2003
workbook in compatibility mode.

so, i've resorted to change the code to this and it seems to resolve the
issue:
ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row

seems excel is sometimes thinking it has a million rows, when in fact
there
are only 65000, so it debugs. seems to happen when i open another workbook
with code.

anyone seen this or have a better way?