Find The Last Used Row

[Minitman]

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

 

[Tim Zych]

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

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

 

[JLGWhiz]

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

 

[FSt1]

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

 

[JLGWhiz]

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]

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

 

[Minitman]

Hey JLGWhiz,

Thanks for the reply.

Your modification works great.

Thanks.

-Minitman

 

[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

 

[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

 

[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

 

[FSt1]

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