How to count number of rows with data?

F

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.
 
S

smartin

FSPH said:
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
 
R

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.
 
G

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
 
F

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
 
R

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
 
F

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
 
R

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)?
 
F

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top