Greetings,

I am trying to display the last row in column A in a MsgBox with this
code on a CommandButton with this code:
______________________________________________
Private Sub CommandButton1_Click()
Dim lLastRow As Long
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
MsgBox "Last row plus 1 = " & lLastRow
End Sub
______________________________________________

The MsgBox says "Last Row plus 1 = 0"

There are 2913 rows in column A..

Can anyone see what is wrong with this code?

Any help will be appreciated.

-Minitman

You're seeing the value in the last cell. You want

....End(xlUp).Rows.Offset(1, 0).Row


--
Tim Zych
www.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility



"Minitman" wrote in message
...
Greetings,

I am trying to display the last row in column A in a MsgBox with this
code on a CommandButton with this code:
______________________________________________
Private Sub CommandButton1_Click()
Dim lLastRow As Long
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
MsgBox "Last row plus 1 = " & lLastRow
End Sub
______________________________________________

The MsgBox says "Last Row plus 1 = 0"

There are 2913 rows in column A..

Can anyone see what is wrong with this code?

Any help will be appreciated.

-Minitman

changed Rows to Row and changed Offset to + 1

Sub Comman()
Dim lLastRow As Long
lLastRow = Sheets(1).Cells("65536", _
"A").End(xlUp).Row + 1
MsgBox "Last row plus 1 = " & lLastRow
End Sub


"Minitman" wrote:

Greetings,

I am trying to display the last row in column A in a MsgBox with this
code on a CommandButton with this code:
______________________________________________
Private Sub CommandButton1_Click()
Dim lLastRow As Long
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
MsgBox "Last row plus 1 = " & lLastRow
End Sub
______________________________________________

The MsgBox says "Last Row plus 1 = 0"

There are 2913 rows in column A..

Can anyone see what is wrong with this code?

Any help will be appreciated.

-Minitman

hi
llastrow is declared as a long which is a number.
the offset method applies to a range object so you have a type mismatch.
instead of....
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
use...
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows + 1

regards
FSt1

"Minitman" wrote:

Greetings,

I am trying to display the last row in column A in a MsgBox with this
code on a CommandButton with this code:
______________________________________________
Private Sub CommandButton1_Click()
Dim lLastRow As Long
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
MsgBox "Last row plus 1 = " & lLastRow
End Sub
______________________________________________

The MsgBox says "Last Row plus 1 = 0"

There are 2913 rows in column A..

Can anyone see what is wrong with this code?

Any help will be appreciated.

-Minitman

If you want to use Offset, then you don't need Rows at all, but use Row on
the end.

Sub Comman()
Dim lLastRow As Long
lLastRow = Sheets(1).Cells("65536", _
"A").End(xlUp).Offset(1, 0).Row
MsgBox "Last row plus 1 = " & lLastRow
End Sub

Since you are trying to establish only the row number of the first available
blank cell then you can add the extra row to the cell referenced by the End
statement with offset, or add 1 to the Row designation which has a numeric
value. They give the same answer.


"Minitman" wrote:

Greetings,

I am trying to display the last row in column A in a MsgBox with this
code on a CommandButton with this code:
______________________________________________
Private Sub CommandButton1_Click()
Dim lLastRow As Long
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
MsgBox "Last row plus 1 = " & lLastRow
End Sub
______________________________________________

The MsgBox says "Last Row plus 1 = 0"

There are 2913 rows in column A..

Can anyone see what is wrong with this code?

Any help will be appreciated.

-Minitman

Hey Tim,

Thanks for the reply.

That explains what was going on!!!

That is useful information, thanks.

The tip about Rows to Row seems to be the key, at least it is now
working.

-Minitman

On Fri, 6 Jun 2008 15:02:35 -0700, "Tim Zych"
<tzych@NOSp@mE@RTHLINKDOTNET wrote:

You're seeing the value in the last cell. You want

...End(xlUp).Rows.Offset(1, 0).Row

Hey JLGWhiz,

Thanks for the reply.

Your modification works great.

Thanks.

-Minitman


On Fri, 6 Jun 2008 15:04:07 -0700, JLGWhiz
wrote:

changed Rows to Row and changed Offset to + 1

Sub Comman()
Dim lLastRow As Long
lLastRow = Sheets(1).Cells("65536", _
"A").End(xlUp).Row + 1
MsgBox "Last row plus 1 = " & lLastRow
End Sub


"Minitman" wrote:

Greetings,

I am trying to display the last row in column A in a MsgBox with this
code on a CommandButton with this code:
______________________________________________
Private Sub CommandButton1_Click()
Dim lLastRow As Long
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
MsgBox "Last row plus 1 = " & lLastRow
End Sub
______________________________________________

The MsgBox says "Last Row plus 1 = 0"

There are 2913 rows in column A..

Can anyone see what is wrong with this code?

Any help will be appreciated.

-Minitman

Hey FSt1,

Thanks for the reply.

Your solution will only work if I add .Row to the end of that
statement like this:
__________________________________________________ _
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0).Row
__________________________________________________ _

The key was the "s" on Rows, which made it a type mismatch, until the
addition of .Row at the tail end. Now this modification works.

However, I do appreciate the attempt.

-Minitman


On Fri, 6 Jun 2008 15:06:01 -0700, FSt1
wrote:

hi
llastrow is declared as a long which is a number.
the offset method applies to a range object so you have a type mismatch.
instead of....
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
use...
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows + 1

regards
FSt1

"Minitman" wrote:

Greetings,

I am trying to display the last row in column A in a MsgBox with this
code on a CommandButton with this code:
______________________________________________
Private Sub CommandButton1_Click()
Dim lLastRow As Long
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
MsgBox "Last row plus 1 = " & lLastRow
End Sub
______________________________________________

The MsgBox says "Last Row plus 1 = 0"

There are 2913 rows in column A..

Can anyone see what is wrong with this code?

Any help will be appreciated.

-Minitman

Hey JLGWhiz,

Actually what I need is the row number of the first available row. I
am using a ComboBox from the Forms menu which has a linked cell. This
ComboBox places the row number of the chosen item into this linked
cell (which I have named "pfDisc"). I discovered that I can change
the number in this linked cell and the item that is in the row number
that matches the number I placed into the linked cell appears in the
ComboBox window. There are 80 cells that are tied to the ComboBox and
change with it. This lastrow is an attempt to prevent the user from
overwriting the data on the chosen row (he did once wiped out the row
I was using as a template for formatting new records. It took a couple
of hours to find and repair the damage - GRRRRR). There are probably
several different ways to do this, but this is the one I came up with.
After all is said and I now have a working model for the next step.
And pretty good idea as to how to continue.

Again, thank you for your help. You and Tim.

-Minitman


On Fri, 6 Jun 2008 15:32:01 -0700, JLGWhiz
wrote:

If you want to use Offset, then you don't need Rows at all, but use Row on
the end.

Sub Comman()
Dim lLastRow As Long
lLastRow = Sheets(1).Cells("65536", _
"A").End(xlUp).Offset(1, 0).Row
MsgBox "Last row plus 1 = " & lLastRow
End Sub

Since you are trying to establish only the row number of the first available
blank cell then you can add the extra row to the cell referenced by the End
statement with offset, or add 1 to the Row designation which has a numeric
value. They give the same answer.


"Minitman" wrote:

Greetings,

I am trying to display the last row in column A in a MsgBox with this
code on a CommandButton with this code:
______________________________________________
Private Sub CommandButton1_Click()
Dim lLastRow As Long
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
MsgBox "Last row plus 1 = " & lLastRow
End Sub
______________________________________________

The MsgBox says "Last Row plus 1 = 0"

There are 2913 rows in column A..

Can anyone see what is wrong with this code?

Any help will be appreciated.

-Minitman

OPPS!!!

That didn't work! I forgot that the ComboBox is filled with a dynamic
named range and my little trick only sent the ComboBox focus to the
last used row not the lastrow +1. I tried +2 and still got only the
lastrow. GRRRR.

Oh well, back to the drawing board.

-Minitman

On Fri, 06 Jun 2008 19:09:37 -0500, Minitman
wrote:

Hey JLGWhiz,

Actually what I need is the row number of the first available row. I
am using a ComboBox from the Forms menu which has a linked cell. This
ComboBox places the row number of the chosen item into this linked
cell (which I have named "pfDisc"). I discovered that I can change
the number in this linked cell and the item that is in the row number
that matches the number I placed into the linked cell appears in the
ComboBox window. There are 80 cells that are tied to the ComboBox and
change with it. This lastrow is an attempt to prevent the user from
overwriting the data on the chosen row (he did once wiped out the row
I was using as a template for formatting new records. It took a couple
of hours to find and repair the damage - GRRRRR). There are probably
several different ways to do this, but this is the one I came up with.
After all is said and I now have a working model for the next step.
And pretty good idea as to how to continue.

Again, thank you for your help. You and Tim.

-Minitman


On Fri, 6 Jun 2008 15:32:01 -0700, JLGWhiz
wrote:

If you want to use Offset, then you don't need Rows at all, but use Row on
the end.

Sub Comman()
Dim lLastRow As Long
lLastRow = Sheets(1).Cells("65536", _
"A").End(xlUp).Offset(1, 0).Row
MsgBox "Last row plus 1 = " & lLastRow
End Sub

Since you are trying to establish only the row number of the first available
blank cell then you can add the extra row to the cell referenced by the End
statement with offset, or add 1 to the Row designation which has a numeric
value. They give the same answer.


"Minitman" wrote:

Greetings,

I am trying to display the last row in column A in a MsgBox with this
code on a CommandButton with this code:
______________________________________________
Private Sub CommandButton1_Click()
Dim lLastRow As Long
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
MsgBox "Last row plus 1 = " & lLastRow
End Sub
______________________________________________

The MsgBox says "Last Row plus 1 = 0"

There are 2913 rows in column A..

Can anyone see what is wrong with this code?

Any help will be appreciated.

-Minitman

hi
no the key was the offset. you have convinced yourself that you need it.
my way produces the same results as the way you just posted. I just tested
both ways. my way has less typing.
but the important thing is that you now have a way that works.
good luck

Regards
FSt1

"Minitman" wrote:

Hey FSt1,

Thanks for the reply.

Your solution will only work if I add .Row to the end of that
statement like this:
__________________________________________________ _
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0).Row
__________________________________________________ _

The key was the "s" on Rows, which made it a type mismatch, until the
addition of .Row at the tail end. Now this modification works.

However, I do appreciate the attempt.

-Minitman


On Fri, 6 Jun 2008 15:06:01 -0700, FSt1
wrote:

hi
llastrow is declared as a long which is a number.
the offset method applies to a range object so you have a type mismatch.
instead of....
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
use...
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows + 1

regards
FSt1

"Minitman" wrote:

Greetings,

I am trying to display the last row in column A in a MsgBox with this
code on a CommandButton with this code:
______________________________________________
Private Sub CommandButton1_Click()
Dim lLastRow As Long
lLastRow = Sheets("CustList").Cells("65536", _
"A").End(xlUp).Rows.Offset(1, 0)
MsgBox "Last row plus 1 = " & lLastRow
End Sub
______________________________________________

The MsgBox says "Last Row plus 1 = 0"

There are 2913 rows in column A..

Can anyone see what is wrong with this code?

Any help will be appreciated.

-Minitman