Count Used Rows

[Sylvan]

I need to count the number of used rows in a range. The two problems are that I can't use VBA -- must use a formula, and I can't be sure that any particular cell in the range will have any information in it.

Right now I'm using:

=IF(COUNTA(OFFSET(A17,1,0,5000,10))=0,0,SUM(COUNTA(OFFSET(A17,1,0,5000,10))/COUNTA(OFFSET(A17,1,0,1,10))))

but this only works if all rows have the same number of used cells. I would use nested ifs, but there are ten columns and you can only use 7 nested ifs.

Any ideas?

[contaminated]

Can u use UDF which will return desired result?
Smth Like below
=CELLCOUNT(A:J)

EDIT: misread.. you said no VBA(((

[contaminated]

Maybe this one will help you.
=MAX(INDEX((A17:J5000<>"")*ROW(A17:J5000),0))
This formula find the last used row number..

[Sylvan]

That works until I run VBA code which pastes into a17:j5000 and then j5000 turns into j17 and I get stuck with a result of 17. What am I doing wrong?

[contaminated]

which vba code. but u said no vba...

[Sylvan]

Sub CombineSheetsData()
Dim rCell As Range
Dim sFind As String
Dim iLook As Integer

Application.ScreenUpdating = False

' Clear destination range
Range("CombinedResults").Offset(2, 0).Resize(5000).EntireRow.Delete shift:=xlUp

' Make sure number of results on each sheet are counted
Application.Calculate

' Check Columns to copy for criteria, copy and paste criteria columns from each of three sheets to Combined sheet
For Each rCell In Range("Criteria")
sFind = Trim(rCell.Value)
If sFind <> "" Then
' Get Column from Sheet1
If Range("Sheet1Results").Value > 0 Then
iLook = WorksheetFunction.Match(sFind, Range("Sheet1Header"), 0)
Range("sheet1results").Offset(2, iLook - 1).Resize(Range("Sheet1Results").Value).Copy _
Range("Combinedresults").Offset(2, iLook - 1)
End If
' Get Column from Sheet2
If Range("Sheet2Results").Value > 0 Then
iLook = WorksheetFunction.Match(sFind, Range("Sheet2Header"), 0)
Range("sheet2results").Offset(2, iLook - 1).Resize(Range("Sheet2Results").Value).Copy _
Range("Combinedresults").Offset(Range("Sheet1Results").Value + 2, iLook - 1)
End If
' Get Column from Sheet3
If Range("Sheet3Results").Value > 0 Then
iLook = WorksheetFunction.Match(sFind, Range("Sheet3Header"), 0)
Range("sheet3results").Offset(2, iLook - 1).Resize(Range("Sheet3Results").Value).Copy _
Range("Combinedresults").Offset(Range("Sheet1Results").Value + _
Range("Sheet2Results").Value + 2, iLook - 1)
End If
End If
Next rCell

' Activate first cell of Combined results
Sheets("Combined").Activate
Range("CombinedResults").Offset(2, 0).Select

End Sub

[Sylvan]

I just meant I wanted to find the row count without VBA, not that the workbook didn't have any VBA.

[Sylvan]

Found something really fancy (array entered):

=SUM(--(MMULT(--(INDIRECT("A1:J20")<>""),TRANSPOSE(COLUMN(INDIRECT("A1:J20"))^0))>0))

Thanks for your help though.

[martindwilson]

oops totally wrong idea