Blewyn

This will only work with a contiguous range as it is using the COUNTA
worksheet function which counts non blank cells, so if you have data in
A1:A26 with A16 blank for example, Number_of_rows variable will end up at 25
and so A25 will be activated, when actually it is A26 that is the last cell.

I prefer to use

Sub test()
Range("A65536").End(xlUp).Select
End Sub

You can equally use any properties or methods of the range object, so
replacing Select with Row will return the row number if that is what is
desired.

People have lots of way of doing this so no way is right or wrong, but
generally I have found the way above reliable. (For versions before XL97 you
will need to lower the start row to 16384 to account for less rows in these
versions)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Blewyn" wrote in message
...

Number_of_rows = WorksheetFunction.CountA(Range("A1:A" & Rows.Count))

Cells(Number_of_rows,1).Activate

Cheers,

Blewyn

PS This advice given to me by Ron de Bruin in response to a smiliar
question a couple of days ago.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

This is a Cool! Solution!! Anyone reading this thread, forget about my
suggestion; this is much better.

I sure wish it was easier to find things like this in help. Thinking of how
often I have to deal with this problem, I'd think that it's a common problem
for everyone; yet I can never find a thing about it in help.

Thanks!


"Nick Hodge" wrote in message
...
Blewyn

This will only work with a contiguous range as it is using the COUNTA
worksheet function which counts non blank cells, so if you have data in
A1:A26 with A16 blank for example, Number_of_rows variable will end up at
25
and so A25 will be activated, when actually it is A26 that is the last
cell.

I prefer to use

Sub test()
Range("A65536").End(xlUp).Select
End Sub

You can equally use any properties or methods of the range object, so
replacing Select with Row will return the row number if that is what is
desired.

People have lots of way of doing this so no way is right or wrong, but
generally I have found the way above reliable. (For versions before XL97
you
will need to lower the start row to 16384 to account for less rows in
these
versions)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Blewyn" wrote in message
...

Number_of_rows = WorksheetFunction.CountA(Range("A1:A" & Rows.Count))

Cells(Number_of_rows,1).Activate

Cheers,

Blewyn

PS This advice given to me by Ron de Bruin in response to a smiliar
question a couple of days ago.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

It is even cooler not to code the 65536 into the solution, but use

Range("A" & Rows.Count).End(xlUp).Select

or

Cells(Rows.Count,"A").End(xlUp).Select

--

HTH

RP

"SixSigmaGuy" wrote in message
...
This is a Cool! Solution!! Anyone reading this thread, forget about my
suggestion; this is much better.

I sure wish it was easier to find things like this in help. Thinking of
how
often I have to deal with this problem, I'd think that it's a common
problem
for everyone; yet I can never find a thing about it in help.

Thanks!


"Nick Hodge" wrote in message
...
Blewyn

This will only work with a contiguous range as it is using the COUNTA
worksheet function which counts non blank cells, so if you have data in
A1:A26 with A16 blank for example, Number_of_rows variable will end up
at
25
and so A25 will be activated, when actually it is A26 that is the last
cell.

I prefer to use

Sub test()
Range("A65536").End(xlUp).Select
End Sub

You can equally use any properties or methods of the range object, so
replacing Select with Row will return the row number if that is what is
desired.

People have lots of way of doing this so no way is right or wrong, but
generally I have found the way above reliable. (For versions before XL97
you
will need to lower the start row to 16384 to account for less rows in
these
versions)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Blewyn" wrote in message
...

Number_of_rows = WorksheetFunction.CountA(Range("A1:A" & Rows.Count))

Cells(Number_of_rows,1).Activate

Cheers,

Blewyn

PS This advice given to me by Ron de Bruin in response to a smiliar
question a couple of days ago.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

Good Point.


"Bob Phillips" wrote in message
...
It is even cooler not to code the 65536 into the solution, but use

Range("A" & Rows.Count).End(xlUp).Select

or

Cells(Rows.Count,"A").End(xlUp).Select

--

HTH

RP

"SixSigmaGuy" wrote in message
...
This is a Cool! Solution!! Anyone reading this thread, forget about my
suggestion; this is much better.

I sure wish it was easier to find things like this in help. Thinking of
how
often I have to deal with this problem, I'd think that it's a common
problem
for everyone; yet I can never find a thing about it in help.

Thanks!


"Nick Hodge" wrote in message
...
Blewyn

This will only work with a contiguous range as it is using the COUNTA
worksheet function which counts non blank cells, so if you have data
in
A1:A26 with A16 blank for example, Number_of_rows variable will end up
at
25
and so A25 will be activated, when actually it is A26 that is the last
cell.

I prefer to use

Sub test()
Range("A65536").End(xlUp).Select
End Sub

You can equally use any properties or methods of the range object, so
replacing Select with Row will return the row number if that is what
is
desired.

People have lots of way of doing this so no way is right or wrong, but
generally I have found the way above reliable. (For versions before
XL97
you
will need to lower the start row to 16384 to account for less rows in
these
versions)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Blewyn" wrote in message
...

Number_of_rows = WorksheetFunction.CountA(Range("A1:A" &
Rows.Count))

Cells(Number_of_rows,1).Activate

Cheers,

Blewyn

PS This advice given to me by Ron de Bruin in response to a smiliar
question a couple of days ago.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/