Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel 2010 question regarding determining the last row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel 2010 question regarding determining the last row
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel 2010 question regarding determining the last row
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel 2010 question regarding determining the last row
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining the last row
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel 2010 question regarding determining the last row
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel 2010 question regarding determining the last row
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2010 | Excel Discussion (Misc queries) | |||
Excel 2010 | Excel Discussion (Misc queries) | |||
Form 4 Range of time from 1/20/2010 4:00 AM To 1/21/2010 10:00 AM | Excel Worksheet Functions | |||
Can't open workbook in excel 64 bit 2010 - but can in excel 2010 3 | Excel Programming |