finding last line in macro

[Guest]

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.

 

[Guest]

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

 

[keepITcool]

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 :

 

[Guest]

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]

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 :

 

[RB Smissaert]

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]

apparently the spreadsheet object in webcomponents has lot's more
rows... and what i hear the current attitude is to listen to the mvps.

ofcourse the drawback is that some people will make sheets that will
take 'a bit of time' to save and recalc.

and ms has to sort out the usedrange muddle. copying some formats
down a 1million row column.. just might upset the calculation engine.





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


RB Smissaert wrote :

 

[RB Smissaert]

I did some searching for information about the web component spreadsheet
control, but couldn't find much.
I have situations where an array has to be displayed in the sheet, but has
too many rows and/or columns.
I have used Access for this now, but not everybody has Access. Also Access
tables have a 255 field limit.

RBS