How do I return which row the active cell is in?

Before I start, thanks to all the people who've already helped me on
this board. Solely because of you, I've managed to complete 10% of a
simple but large project (2 applications so far). I've hunted the
boards now for a couple of hours and haven't found the answer to a very
simple issue. What I want to do is this:

I have a worksheet with data already on it. I want to cut and paste
data from another worksheet. I need to identify the first empty row at
the end of the data and return its row number. I tried the following.

Sub test()
Dim lRowNum As Long

Range("A65536").End(xlUp).Offset(1, 0).Activate
lRowNum = ActiveCell.Row
MsgBox "lRowNum"
End Sub

MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
I get my row number?

Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
smell. Why didn't something crash burn and die at lRowNum =
ActiveCell.Row?

Thanks now for any replies.

Yours, again, in frustrated confusion,

Terry R.

"Cloudfall" <[email protected]> wrote in message
news:[email protected]...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
> Dim lRowNum As Long
>
> Range("A65536").End(xlUp).Offset(1, 0).Activate
> lRowNum = ActiveCell.Row
> MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.

Try MsgBox ActiveCell.Address

Bruno

"Cloudfall" <[email protected]> wrote in message
news:[email protected]...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
> Dim lRowNum As Long
>
> Range("A65536").End(xlUp).Offset(1, 0).Activate
> lRowNum = ActiveCell.Row
> MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.

Forget my previous message, I misunderstood your question.
You write MsgBox "lRowNumber"; why those ""?
MsgBox lRowNum should work, or simply MsgBox ActiveCell.Row.

Bruno

You are displaying a text string, not the variable lRowNum. It displays that
text for me, not OK.

You don't need to activate the last row, just use

Dim lRowNum As Long

lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
MsgBox lRowNum


--
HTH

Bob Phillips

"Cloudfall" <[email protected]> wrote in message
news:[email protected]...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
> Dim lRowNum As Long
>
> Range("A65536").End(xlUp).Offset(1, 0).Activate
> lRowNum = ActiveCell.Row
> MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.
>

Bruno,

Your way is wrong. Dave's way is correct, and not just "a preference". The default property of a
range object is .Value, so your code will return the value of the last cell + 1. If that value is a
number, you will get that number +1, not the row number +1. If the value is a string, you will get
an error.

Since you want the row number, use .Row

HTH,
Bernie
MS Excel MVP


"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>> Maybe...
>>
>> MsgBox Range("A" & Rows.Count).End(xlUp).row + 1
>
> If you prefer, but it also works without .Row:
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
>
> Bruno
>

It depends on what's in that cell.

I'm betting you just put 1, 2, 3, .... in your test data.

Bruno Campanini wrote:
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > Maybe...
> >
> > MsgBox Range("A" & Rows.Count).End(xlUp).row + 1
>
> If you prefer, but it also works without .Row:
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
>
> Bruno

--

Dave Peterson

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Maybe...
>
> MsgBox Range("A" & Rows.Count).End(xlUp).row + 1

If you prefer, but it also works without .Row:
MsgBox Range("A" & Rows.Count).End(xlUp) + 1

Bruno

"Jef Gorbach" <[email protected]> wrote in message
news:[email protected]...

> your close, but change to: IRowNum = Range("A65536").End(xlUp).Offset(1,
> 0).Row

You'r right, the shorter:
MsgBox [A65536].End(xlUp) + 1

Bruno

Maybe...

MsgBox Range("A" & Rows.Count).End(xlUp).row + 1



Bruno Campanini wrote:
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
>
> > You are displaying a text string, not the variable lRowNum. It displays
> > that
> > text for me, not OK.
> >
> > You don't need to activate the last row, just use
> >
> > Dim lRowNum As Long
> >
> > lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> > MsgBox lRowNum
>
> You don't even need three lines of code:
>
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
>
> Bruno

--

Dave Peterson

"Cloudfall" <[email protected]> wrote in message
news:[email protected]...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
> Dim lRowNum As Long
>
> Range("A65536").End(xlUp).Offset(1, 0).Activate
> lRowNum = ActiveCell.Row
> MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.
>

your close, but change to: IRowNum = Range("A65536").End(xlUp).Offset(1,
0).Row

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...

> You are displaying a text string, not the variable lRowNum. It displays
> that
> text for me, not OK.
>
> You don't need to activate the last row, just use
>
> Dim lRowNum As Long
>
> lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> MsgBox lRowNum

You don't even need three lines of code:

MsgBox Range("A" & Rows.Count).End(xlUp) + 1

Bruno

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Bruno,
>
> Your way is wrong. Dave's way is correct, and not just "a preference".
> The default property of a range object is .Value, so your code will return
> the value of the last cell + 1. If that value is a number, you will get
> that number +1, not the row number +1. If the value is a string, you will
> get an error.
>
> Since you want the row number, use .Row

You are perfectly right!
I tested in a column terminating with the corresponding row number...

Arghhhh

Bruno

You should certainly not omit the

Dim lRowNum As Long

very poor coding practice

--
HTH

Bob Phillips

"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
>
> > You are displaying a text string, not the variable lRowNum. It displays
> > that
> > text for me, not OK.
> >
> > You don't need to activate the last row, just use
> >
> > Dim lRowNum As Long
> >
> > lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> > MsgBox lRowNum
>
> You don't even need three lines of code:
>
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
>
> Bruno
>
>

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...

> You should certainly not omit the
>
> Dim lRowNum As Long
>
> very poor coding practice

I've NEVER used lRowNum on my examples,
only MsgBox Range...
Or, if I did, it was only with reference to Terry's code.

Is this a good code:
----------------------------
Dim lRowNum As Long

lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
MsgBox lRowNum
------------------------------

compared with this one?
-----------------------------
MsgBox Range("A" & Rows.Count).End(xlUp).Row + 1
-----------------------------

Your code reveals a complete omologation to the
mass of fantasyless programmers:
Blank lines everywhere.
Indentention when there is no need for indent.
Code redundancy (Offset can be replaced by +1 at
the end of line)

And - I suppose -
Dim ... as Variant
Option Base 0
Range("A1").Value
Application.WorksheetFunction
etc.

Art of communication has much more to deal with
quality than with quantity.
Software vendors don't.

Well Bob, since now on I'd like to be very sympathetic with you.
Hopefully, would you be the same?

Bruno

Bruno,

Thank you. Both worked. Have a good one.

Terry R.

Bob,

Thank you for your response. I really like the way that you can return
the number of the row without actually having to go there. So I started
to experiment with your code and got a weird anomaly. I cut and pasted
your exact code into a small test subroutine.

I put the following data into a new worksheet:
The number 1 into cell A1, the number 2 into cell B2, and the number
three into cell C3.

When I ran the code "lRowNum = Range("A" &
Rows.Count).End(xlUp).Offset(1, 0).Row" for column A, I got the answer
"2", as you would expect. Similarly, for "lRowNum = Range("B" &
Rows.Count).End(xlUp).Offset(1, 0).Row I got 3, and for "lRowNum =
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row" I got 4.

But when I ran "lRowNum = Range("D" & Rows.Count).End(xlUp).Offset(1,
0).Row", with nothing in column D, I got back 2. The same was true for
every other column with nothing in it.

Do you think this is because of the "Offset(1, 0)" factor? So, to use
this reliably, even for columns which have nothing in them, would you
require further code?

Still slightly confused, yours sincerely

Terry R.

Bob's code is like selecting the last cell in column D. Then hitting the End
key and up arrow (to find that last used cell. If column D is empty, end
followed by uparrow gets you to D1. .offset(1,0) will drop you down 1.

You could use a minor variation.

Dim NextCell as Range
dim NextRow as long
with worksheets("sheet1")
set Nextcell = .cells(.rows.count,"D").end(xlup)
if isempty(Nextcell) then
'do nothing, don't change anything
else
set nextcell = nextcell.offset(1,0)
end if
nextrow = nextcell.row 'if you need it
end with

=========
But now you may have another problem--what happens if there's data in
D65536????)

<vbg>


Cloudfall wrote:
>
> Bob,
>
> Thank you for your response. I really like the way that you can return
> the number of the row without actually having to go there. So I started
> to experiment with your code and got a weird anomaly. I cut and pasted
> your exact code into a small test subroutine.
>
> I put the following data into a new worksheet:
> The number 1 into cell A1, the number 2 into cell B2, and the number
> three into cell C3.
>
> When I ran the code "lRowNum = Range("A" &
> Rows.Count).End(xlUp).Offset(1, 0).Row" for column A, I got the answer
> "2", as you would expect. Similarly, for "lRowNum = Range("B" &
> Rows.Count).End(xlUp).Offset(1, 0).Row I got 3, and for "lRowNum =
> Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row" I got 4.
>
> But when I ran "lRowNum = Range("D" & Rows.Count).End(xlUp).Offset(1,
> 0).Row", with nothing in column D, I got back 2. The same was true for
> every other column with nothing in it.
>
> Do you think this is because of the "Offset(1, 0)" factor? So, to use
> this reliably, even for columns which have nothing in them, would you
> require further code?
>
> Still slightly confused, yours sincerely
>
> Terry R.

--

Dave Peterson

Dave,

It worked. Thank you for the warning re data in D65536. You once gave
me a warning in another post:
"If you have formulas in the range, this will keep them as
formulas--your code:
selection.value = selection.value
would cause damage if you're not careful."
Sure enough, I wound up doing that on one occasion. As I was about to
post another question, your warning came back to me and I came up with
a workaround.

Thanks for your help,

Terry R.

Jef,

Thanks, this works great.

Terry R.

Bruno,

This takes the value in the last cell and adds "1" to it.

Thank you for your response,

Terry R.

Glad you got things working.

Cloudfall wrote:
>
> Dave,
>
> It worked. Thank you for the warning re data in D65536. You once gave
> me a warning in another post:
> "If you have formulas in the range, this will keep them as
> formulas--your code:
> selection.value = selection.value
> would cause damage if you're not careful."
> Sure enough, I wound up doing that on one occasion. As I was about to
> post another question, your warning came back to me and I came up with
> a workaround.
>
> Thanks for your help,
>
> Terry R.

--

Dave Peterson