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

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

HTH
Cordially
Pascal


"Fad" a écrit dans le message de news:
...
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

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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

HTH
Cordially
Pascal


"Fad" a écrit dans le message de news:
...
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

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

Cordially
Pascal

"David McRitchie" a écrit dans le message de news:
...
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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

HTH
Cordially
Pascal


"Fad" a écrit dans le message de news:
...
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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Fad" wrote in message ...
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