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

  • Thread starter Thread starter Cloudfall
  • Start date Start date
C

Cloudfall

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 said:
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.
Click to expand...

Try MsgBox ActiveCell.Address

Bruno
 
Cloudfall said:
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.
Click to expand...

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
 
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
Click to expand...

You don't even need three lines of code:

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

Bruno
 
Cloudfall said:
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.
Click to expand...

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

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

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,

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
 
Bernie Deitrick said:
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
Click to expand...

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
Click to expand...

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
 
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>
 
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.
 
Bruno,

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

Thank you for your response,

Terry R.
 
Back
Top