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?
--
Ken Hudson

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

"Ken Hudson" wrote:

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?
--
Ken Hudson

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?
--
Ken Hudson

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.)

Jim Cone wrote:

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?
--
Ken Hudson

--

Dave Peterson

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.)

I see your point (now!).

Jim Cone wrote:

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.)

--

Dave Peterson

On Wed, 8 Oct 2008 16:58:10 -0700, Ken Hudson
wrote:

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?

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

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.

--
Ken Hudson


"Ron Rosenfeld" wrote:

On Wed, 8 Oct 2008 16:58:10 -0700, Ken Hudson
wrote:

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?

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

On Thu, 9 Oct 2008 08:25:01 -0700, Ken Hudson
wrote:

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.

--
Ken Hudson

Interesting.

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

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.
--
Ken Hudson


"Ron Rosenfeld" wrote:

On Thu, 9 Oct 2008 08:25:01 -0700, Ken Hudson
wrote:

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.

--
Ken Hudson

Interesting.

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

What happens in xl2007?

Did you dim dsubrows as long or as an integer?



Ken Hudson wrote:

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?
--
Ken Hudson

--

Dave Peterson

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

--
Ken Hudson


"Dave Peterson" wrote:

What happens in xl2007?

Did you dim dsubrows as long or as an integer?



Ken Hudson wrote:

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?
--
Ken Hudson

--

Dave Peterson

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
--
Jim Cone
Portland, Oregon USA

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


"Jim Cone" wrote:


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
--
Jim Cone
Portland, Oregon USA