excel 2010 question regarding determining the last row

[Gary Keramidas]

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?

 

[Rich Locus]

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]

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.

 

[Gary Keramidas]

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

 

[Geeta Sonawane]

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 i
2010, or 2007, for that matter

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

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

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

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

anyone seen this or have a better way

-

Gary Keramida
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/tutorial...d8-3e2773fa29b5/whats-new-for-developers.aspx

 

[Charles Williams]

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

 

[Gary Keramidas]

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