Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Finding the row number for the last line of data

Status
Not open for further replies.

davetek

Programmer
Oct 30, 2002
42
IE
hi,

i have a column (A) with a list of data. the number of rows contained in column A varies from day to day.
i need to use the number of the last row as a variable.
is there a function that will return an integer with the last row number of data for the column.

thanks
 
Yup - many - lots of different methods in the 2 FAQs on this subject....but they'll return a LONG not an integer as integers only go up to ~325000

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
 
Just to support xlbo, this has been answered many times. Many ways to solve.

I like the post thread707-569602

KenWright used this simple method ....

Sub find_last_row()
Dim lrow As Integer

lrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

End Sub


Check out Loomah's code, its more comprehensive.

Well done guys, good luck.
 
Dim Intmax as Integer

'Find Last Numerical Row in a Table and Return to Variable
'Object_Id = Column Name
'Object = Table Name
intMax = DMax("Object_ID", "Object")
Hope This helps Dave
 
Surely
Code:
lrow = ActiveSheet.UsedRange.Row
is all you need to get the index of the last row (not forgetting to reset UsedRange by the line
Code:
ActiveSheet.UsedRange
which should immediately precede
Code:
lrow = ActiveSheet.UsedRange.Row
)

 
ActiveSheet.UsedRange.Row returns the FIRST row of the used range.

ActiveSheet.UsedRange.Rows.Count will return the last row IF two conditions are met:

1. Row 1 is actually used, and

2. There are no formats set for unused cells below the last row of actual data. (I.e. no number formatting, no formulas, no nothing.)

It is condition number 2 that makes using UsedRange problematical. If your situation meets both conditions, then UsedRange is obviously the easiest way to go.

 
Ooops.

It's a Friday afternoon and someone tweaked my UsedRange melon man.

"Hey George - guess what, I boobed again" (cue canned laughter from the audience - episode #7, series 6 "Avril and Laverne").

 
TopJack - unless you are using xl95 then yup - should be LONG as integer doesn't cover up to 65536

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top