excel 2010 question regarding determining the last row
in [Programming]
|
From: Gary Keramidas on 11 Mar 2010 23:42 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
From: Rich Locus on 12 Mar 2010 00:39 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 > > > . >
From: Rich Locus on 12 Mar 2010 00:44 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 > > > . >
From: Gary Keramidas on 12 Mar 2010 00:55 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" <RichLocus(a)discussions.microsoft.com> wrote in message news:21335E83-C081-40DD-B0DA-0BA080A1318A(a)microsoft.com... > 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 >> >> >> . >>
From: Charles Williams on 12 Mar 2010 04:53 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?
|
Next
|
Last
Pages: 1 2 Prev: Passing Parameters to a Pivot Table Next: Paste to next row if cells are not empty |