Get the number of last row entry

[Guest]

I have a common macro that will run on different files. Since these files
are of different sizes, is there a function that will return the number of
rows in the file instead of going each time and change the number.

Thank you

 

[papou]

Hello Fad
Range("A65536").End(xlUp).Row
Which will give the last non empty row in column A.

HTH
Cordially
Pascal

 

[Ron de Bruin]

Hi Fad

You can use the function on this page
http://www.rondebruin.nl/copy1.htm

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[David McRitchie]

Hi Fad,
Pascal's code correctly provides for skipping over empty cells, but the
65536 would be best not used, in case Excel gets more rows,
it has happened. The number of columns hasn't changed since
Excel first started and is LONG overdue, and if it does get changed
you will have a mixture of people having one or the other for several
years.

The following would be equivalent and would continue to work in future,
and is based on Column A as Pascal's code, not on the lastcell as
maintained (or not) by Excel:

Dim rw as long '-- integer only goes to 32768
rw = Cells(Rows.Count, 1).End(xlUp).Row

Some useful macros that I have on my toolbar making use of this can be found at
http://www.mvps.org/dmcritchie/excel/tools.htm#macros

 

[papou]

Hello David
Yes good thing!
I will try and keep it in mind ;-)

Cordially
Pascal