How to count number of rows with data?

[FSPH]

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.

 

[smartin]

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

 

[AltaEgo]

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

 

[Rick Rothstein]

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.

 

[Gary Keramidas]

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

 

[Rick Rothstein]

See my posting for a shorter way to do this.

 

[Gary Keramidas]

got it
--

Gary Keramidas
Excel 2003

See my posting for a shorter way to do this.

 

[Normek]

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

 

[FSPH]

Hello there,

very useful code. However, how do I search for "*" in a particular column
(e.g., column 10)?

Thanks for your help.

FSPH

 

[Rick Rothstein]

Well, if you want to search for the last character in a known column, I
would just use the "standard" look up from the bottom method...

LastRowInCol10 = Cells(Rows.Count, 10).End(xlUp).Row

 

[FSPH]

Hello Rick,

for some reason this code doesn't work.

I want to know the number of rows with data in them.

Example: In colum 10 the first 4 rows have data (i.e., something "written")
in them. Here are the data with heading "Data" in the first column:
Data
1
2
3

Given this, I expect LastRowInCol10 to be 4. However, I always get the
result 1. Any idea what I am doing wrong here?

Thank you for your continued help.

FSPH

 

[Rick Rothstein]

Where is you code located at (a sheet code window or a Module code window)?
Is the worksheet with your data the active sheet when you run the code (as
written, the code execute for the active sheet, which we can change if need
be)?

 

[FSPH]

Thanks, Rick, inserting "ActiveSheet" before Cells() did the trick. Yes, the
code was in another worksheet than the one I referred to.

Thank you for your help.

FSPH