Row Count Error

K

Ken Hudson

I have the following code:

dSubRows = Workbooks(strSubWB).Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row

It works in Excel 2003 but not in Excel 2007.
Why not?
 
M

Mike

Here's a function that I use

Private Function FindLastRow(whatSheet As Worksheet, whichCol As String) As
Long
'this finds and returns the actual last row on a sheet
'that has entry in specified column
'NOT the next row available for data entry
'so calling routine should add 1 to the returned value
'to determine next row available for new entry
'when it is found that a sheet has no entries, this
'routine will (properly) return zero.

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
FindLastRow = whatSheet.Range(whichCol & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 or later
FindLastRow = whatSheet.Range(whichCol &
Rows.CountLarge).End(xlUp).Row
End If
If FindLastRow = 1 And IsEmpty(whatSheet.Range(whichCol & "1")) Then
FindLastRow = 0 ' no entries at all in the column on the sheet
End If

End Function
 
J

Jim Cone

It doesn't work???
Rows.Count is unqualified, so it refers to the active sheet.
Possibly the active sheet is different in xl2007 then in xl2003.
In any case I would use something like this...

With Workbooks(strSubWB).Sheets(1)
dSubRows = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

Or the problem, whatever it is, could be an "improvement" added in xl2007
--
Jim Cone
Portland, Oregon USA


"Ken Hudson"
wrote in message
I have the following code:
dSubRows = Workbooks(strSubWB).Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
It works in Excel 2003 but not in Excel 2007.
Why not?
 
R

Ron Rosenfeld

I have the following code:

dSubRows = Workbooks(strSubWB).Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row

It works in Excel 2003 but not in Excel 2007.
Why not?
Click to expand...

What do you mean when you write it does not work?

XL crashes?
Wrong result?
Some error message?

Since an equivalent to the right side of your statement works in my XL2007, the
problem is likely in something you have not told us about.

For example, this statement works as expected:

Debug.Print ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row

printing the number of the row just below the lowest occupied cell in column A.
--ron
 
J

Jim Cone

In xl2007, you can have workbooks with 65000 rows and workbooks with
1,000,000 rows open at the same time.
Code with an unqualified Row.Count could provide some entertainment. <g>
 
D

Dave Peterson

Although I think it's always a good idea to qualify the ranges (including
..rows.count), I don't think it's the problem in this case.

Each worksheet in the workbook will have the same number of rows-- either 64k or
1M rows--depending on how it was saved and reopened.

(The number of rows between worksheets in the same workbook won't vary.)
 
J

Jim Cone

Hi Dave,
But you could have code in one workbook operating on another workbook and
Ken, in his original post, specifies a particular workbook.
However,he doesn't say where the code is located.
See my second post.
--
Jim Cone
Portland, Oregon USA
(xl2007 is a downgrade)


"Dave Peterson"
wrote in message
Although I think it's always a good idea to qualify the ranges (including
..rows.count), I don't think it's the problem in this case.
Each worksheet in the workbook will have the same number of rows--
either 64k or 1M rows--depending on how it was saved and reopened.
(The number of rows between worksheets in the same workbook won't vary.)
 
D

Dave Peterson

I see your point (now!).

Jim said:
Hi Dave,
But you could have code in one workbook operating on another workbook and
Ken, in his original post, specifies a particular workbook.
However,he doesn't say where the code is located.
See my second post.
--
Jim Cone
Portland, Oregon USA
(xl2007 is a downgrade)

"Dave Peterson"
wrote in message
Although I think it's always a good idea to qualify the ranges (including
.rows.count), I don't think it's the problem in this case.
Each worksheet in the workbook will have the same number of rows--
either 64k or 1M rows--depending on how it was saved and reopened.
(The number of rows between worksheets in the same workbook won't vary.)
Click to expand...
 
K

Ken Hudson

Hi Ron,
You are correct, although I didin't know what it was I didn't tell you about
until I did more experimenting. (Is that a double negative or something?)
Anyway, the problem was that the workbook (strSubWB) that I had opened was
from 2003 and had a .xls file extension.
If I open it in 2007 and save as .xlsx, the code works okay.
I have just begun to convert a slew of macros to 2007 and have a lot to
learn it seems.
Thanks all for your input.
 
K

Ken Hudson

It is dimmmed as double.
When I run it, I get error 1004 - Application-defined or object-defined error.
 
K

Ken Hudson

Hey Jim,
I live in Seattle. Maybe I should just drive down and figure this out face
to face. <g>
 
R

Ron Rosenfeld

Hi Ron,
You are correct, although I didin't know what it was I didn't tell you about
until I did more experimenting. (Is that a double negative or something?)
Anyway, the problem was that the workbook (strSubWB) that I had opened was
from 2003 and had a .xls file extension.
If I open it in 2007 and save as .xlsx, the code works okay.
I have just begun to convert a slew of macros to 2007 and have a lot to
learn it seems.
Thanks all for your input.
Click to expand...

Interesting.

I've not been able to reproduce your error here, though.
--ron
 
K

Ken Hudson

Hi Ron,
That's disappointing to hear. It makes me think now that I really haven't
found the smoking gun.
There must be something else that I don't know I haven't told you all yet.
 
Top