Hello there,

Several of my worksheets contain a column (e.g., colum 5) of data with
varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
rows of data. The first two rows are occupied by the header on all worksheets.

How can I count the maximum of rows with data?

I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
but unfortunately it doesn't work properly (probably because I don't
understand what exactly the code is doing).

Thank you, I would be grateful for your help.

FSPH wrote:
Hello there,

Several of my worksheets contain a column (e.g., colum 5) of data with
varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
rows of data. The first two rows are occupied by the header on all worksheets.

How can I count the maximum of rows with data?

I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
but unfortunately it doesn't work properly (probably because I don't
understand what exactly the code is doing).

Thank you, I would be grateful for your help.

Maybe this:

Function CountRows(ByVal MyRange As Range) As Long
CountRows = Application.WorksheetFunction.CountA(MyRange)
End Function

There is some useful code here here for finding the true used range in
Excel:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=82

From that:

Sub lrow()
Dim lastrow
lastrow = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox lastrow
End Sub



--
Steve

"FSPH" wrote in message
...
Hello there,

Several of my worksheets contain a column (e.g., colum 5) of data with
varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
rows of data. The first two rows are occupied by the header on all
worksheets.

How can I count the maximum of rows with data?

I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
but unfortunately it doesn't work properly (probably because I don't
understand what exactly the code is doing).

Thank you, I would be grateful for your help.

Not sure how your code is set up, but the following statement with find the
row number of the last row that has data in it no matter what column that
data is in...

LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

Just change the ActiveSheet reference to whatever you are using to reference
the worksheet you are currently looking at. And I'm guessing, for your
purposes, you would want to subtract 2 from this number to get your count.

--
Rick (MVP - Excel)


"FSPH" wrote in message
...
Hello there,

Several of my worksheets contain a column (e.g., colum 5) of data with
varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
rows of data. The first two rows are occupied by the header on all
worksheets.

How can I count the maximum of rows with data?

I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
but unfortunately it doesn't work properly (probably because I don't
understand what exactly the code is doing).

Thank you, I would be grateful for your help.

not sure what you want, but this will give you the row with the longest
column of data as long as the last column has a header in row 1.
this works on sheet1:

Sub test()
Dim lastcol As Long
Dim RealLastRow As Long
Dim arr As Variant
Dim i As Long
With Worksheets("sheet1")
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
ReDim arr(1 To lastcol)
For i = 1 To lastcol
arr(i) = .Cells(Rows.Count, i).End(xlUp).Row
Next
RealLastRow = Application.Max(arr)
MsgBox RealLastRow
End With
End Sub

--

Gary Keramidas
Excel 2003


"FSPH" wrote in message
...
Hello there,

Several of my worksheets contain a column (e.g., colum 5) of data with
varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
rows of data. The first two rows are occupied by the header on all
worksheets.

How can I count the maximum of rows with data?

I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
but unfortunately it doesn't work properly (probably because I don't
understand what exactly the code is doing).

Thank you, I would be grateful for your help.

See my posting for a shorter way to do this.

--
Rick (MVP - Excel)


"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
not sure what you want, but this will give you the row with the longest
column of data as long as the last column has a header in row 1.
this works on sheet1:

Sub test()
Dim lastcol As Long
Dim RealLastRow As Long
Dim arr As Variant
Dim i As Long
With Worksheets("sheet1")
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
ReDim arr(1 To lastcol)
For i = 1 To lastcol
arr(i) = .Cells(Rows.Count, i).End(xlUp).Row
Next
RealLastRow = Application.Max(arr)
MsgBox RealLastRow
End With
End Sub

--

Gary Keramidas
Excel 2003


"FSPH" wrote in message
...
Hello there,

Several of my worksheets contain a column (e.g., colum 5) of data with
varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
rows of data. The first two rows are occupied by the header on all
worksheets.

How can I count the maximum of rows with data?

I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
but unfortunately it doesn't work properly (probably because I don't
understand what exactly the code is doing).

Thank you, I would be grateful for your help.

got it
--

Gary Keramidas
Excel 2003


"Rick Rothstein" wrote in message
...
See my posting for a shorter way to do this.

--
Rick (MVP - Excel)


"Gary Keramidas" <GKeramidasAtMSN.com wrote in message
...
not sure what you want, but this will give you the row with the longest
column of data as long as the last column has a header in row 1.
this works on sheet1:

Sub test()
Dim lastcol As Long
Dim RealLastRow As Long
Dim arr As Variant
Dim i As Long
With Worksheets("sheet1")
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
ReDim arr(1 To lastcol)
For i = 1 To lastcol
arr(i) = .Cells(Rows.Count, i).End(xlUp).Row
Next
RealLastRow = Application.Max(arr)
MsgBox RealLastRow
End With
End Sub

--

Gary Keramidas
Excel 2003


"FSPH" wrote in message
...
Hello there,

Several of my worksheets contain a column (e.g., colum 5) of data with
varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with
20
rows of data. The first two rows are occupied by the header on all
worksheets.

How can I count the maximum of rows with data?

I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
but unfortunately it doesn't work properly (probably because I don't
understand what exactly the code is doing).

Thank you, I would be grateful for your help.

Thanks smartin,
Your post helped me solve a question I posted today "end(xldown)


"smartin" wrote:

FSPH wrote:
Hello there,

Several of my worksheets contain a column (e.g., colum 5) of data with
varying length. Worksheet 1 may have 10 rows of data, worksheet 2 with 20
rows of data. The first two rows are occupied by the header on all worksheets.

How can I count the maximum of rows with data?

I encountered this code: Cells(Rows.Count, "a").End(xlDown).Row
but unfortunately it doesn't work properly (probably because I don't
understand what exactly the code is doing).

Thank you, I would be grateful for your help.

Maybe this:

Function CountRows(ByVal MyRange As Range) As Long
CountRows = Application.WorksheetFunction.CountA(MyRange)
End Function