Find The Last Used Row

M

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
 
T

Tim Zych

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

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

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
 
F

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
 
J

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

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
 
M

Minitman

Hey JLGWhiz,

Thanks for the reply.

Your modification works great.

Thanks.

-Minitman
 
M

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
 
M

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
 
M

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
 
F

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top