How do I code a macro (or keystroke it) to find the last row that has data in
a column without harcoding the macro to a specific row? I have sections of
data (every 6th row is blank) and I want to be able to find the absolute last
row that has data in it for column B no matter where that last row is. Thanx.

Insert the following near the beging of your code:

Range("B65536").Select
Selection.End(xlUp).Select

If you want the row number:
r = ActiveCell.Row

"GJR3599" wrote:

How do I code a macro (or keystroke it) to find the last row that has data in
a column without harcoding the macro to a specific row? I have sections of
data (every 6th row is blank) and I want to be able to find the absolute last
row that has data in it for column B no matter where that last row is. Thanx.

Glenn,

WHY the selects? Is there a need to write slow code on purpose.?

r= Activesheet.Cells(rows.count,2).end(xlup).row





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Glenn Ray wrote :

Insert the following near the beging of your code:

Range("B65536").Select
Selection.End(xlUp).Select

If you want the row number:
r = ActiveCell.Row

"GJR3599" wrote:

How do I code a macro (or keystroke it) to find the last row that
has data in a column without harcoding the macro to a specific row?
I have sections of data (every 6th row is blank) and I want to be
able to find the absolute last row that has data in it for column B
no matter where that last row is. Thanx.

Not trying to be slow; just trying to show one way of getting the row.

Here's less code by 17 characters:
r = Range("B65536").End(xlUp).Row


"keepITcool" wrote:

Glenn,
WHY the selects? Is there a need to write slow code on purpose.?
r= Activesheet.Cells(rows.count,2).end(xlup).row

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Glenn Ray wrote :

Insert the following near the beging of your code:

Range("B65536").Select
Selection.End(xlUp).Select

If you want the row number:
r = ActiveCell.Row

"GJR3599" wrote:

How do I code a macro (or keystroke it) to find the last row that
has data in a column without harcoding the macro to a specific row?
I have sections of data (every 6th row is blank) and I want to be
able to find the absolute last row that has data in it for column B
no matter where that last row is. Thanx.

Glenn you miss the point:

17 characters less in a line of code dont necessarily mean it's faster.
(although using the global Range object in this case is)

my point was that the 2 SELECT statements in your original reply
truely and unnecessarily slows down the code and necessitates
screenupdating = false

by using Activesheet it's clearer to OP which sheet is used.
by using Rows.count it will work in future versions.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Glenn Ray wrote :

Not trying to be slow; just trying to show one way of getting the
row.

Here's less code by 17 characters:
r = Range("B65536").End(xlUp).Row


"keepITcool" wrote:

Glenn,
WHY the selects? Is there a need to write slow code on purpose.?
r= Activesheet.Cells(rows.count,2).end(xlup).row

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Glenn Ray wrote :

Insert the following near the beging of your code:

Range("B65536").Select
Selection.End(xlUp).Select

If you want the row number:
r = ActiveCell.Row

"GJR3599" wrote:

How do I code a macro (or keystroke it) to find the last row
that has data in a column without harcoding the macro to a
specific row? I have sections of data (every 6th row is blank)
and I want to be able to find the absolute last row that has
data in it for column B no matter where that last row is. Thanx.

by using Rows.count it will work in future versions

Good point.
Do you think MS would ever increase the number of rows and/or columns?
If there are no performance drawbacks from that I wouldn't mind 1000.000
rows and say 1000 columns.

RBS


"keepITcool" wrote in message
.com...
Glenn you miss the point:

17 characters less in a line of code dont necessarily mean it's faster.
(although using the global Range object in this case is)

my point was that the 2 SELECT statements in your original reply
truely and unnecessarily slows down the code and necessitates
screenupdating = false

by using Activesheet it's clearer to OP which sheet is used.
by using Rows.count it will work in future versions.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Glenn Ray wrote :

Not trying to be slow; just trying to show one way of getting the
row.

Here's less code by 17 characters:
r = Range("B65536").End(xlUp).Row


"keepITcool" wrote:

Glenn,
WHY the selects? Is there a need to write slow code on purpose.?
r= Activesheet.Cells(rows.count,2).end(xlup).row

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Glenn Ray wrote :

Insert the following near the beging of your code:

Range("B65536").Select
Selection.End(xlUp).Select

If you want the row number:
r = ActiveCell.Row

"GJR3599" wrote:

How do I code a macro (or keystroke it) to find the last row
that has data in a column without harcoding the macro to a
specific row? I have sections of data (every 6th row is blank)
and I want to be able to find the absolute last row that has
data in it for column B no matter where that last row is. Thanx.