Count number of rows in Excel 2

[akn846]

Could someone please answer this VERY simple question for me.

What I would like to do is to assign to the contents of a cell, a count of the number of rows which exist within a spreadsheet which have data in them.

I thought that I would be able to do this by using the COUNTA function - but it appears that this requires two parameters passed to it in order to return a value, ie if it was the first column I needed to check then I believe that the call to the COUNTA function would be something like COUNTA(A1:A'what ever the row count is')

So what I am trying to work out is how can I determine the number of rows in a spreadsheet - and I can't find the answer anywhere!!

Can someone please put me out of my misery.

Thanks

Andy

 

[Hasit]

Erm....I thought that if you have data in the last cell, which is say A138, then:

=COUNTA(A1:A138)

would give you the number of cells in that range that has content in the cell.

What do you get if you use the above formula?

 

[xlhelp]

You put in a column (other than A),

=counta(A:A)

HTH

Indu

 

[akn846]

HasIT

Many thanks for your response - but the point was that the spreadsheet is going to grow - so I don't want to hard code the number of rows in the spreadsheet.

xlHelp

Many thanks for your suggestion - I shall give this a go - have to say that I have not found any documentation that was able to point me in this direction - are there any online resources for Excel that you know of which I should bookmark in my favourites.

Thanks once again

 

[JohnCHolmes]

Indu is of course right, but as to your original notion (Andy), COUNTA(A1:A65535) would have worked. I hope that doesn't surprise you, but is simply a case of "dohhhh!"

And I believe Indu meant to say you "You *can* put..."

 

[Hasit]

Andy,

I understand the question now. And of course Indu is right in her response. However, in order to ensure that you only have to enter a formula once, and irrespective of whether the data grows length ways (down the column) or widthways (across rows), what you can do is this:

1. Highlight all the cells you want to include in the range yuo want to perform the calculation on

2. Hit CTRL-F3, and then type in the name of the range. Call it what you want, but make sure you don't use the same notation Excel uses, such as "A1". So in this example, perhaps call it "My_range"

3. In a cell, type in =COUNTA(My-range)

So whenever any additional entries are added in any of the cells in "my_range" the count will go up automatically.

 

[xlbo]

As far as standard functions go, the ones given will kinda work but you have to remember that you must insert cells into a\ named range for it to expand - just adding data after it will not cause this. Plus, if you name more than a column, you'll get an incorrect count
Try this simple UDF for size. Copy the code into a standard module (NOT Worksheet or workbook) and then type =LastRow() into a cell on your worksheet

Function LastRow()
Dim lRow As Long, lCol As Integer, holder As Long
Application.Volatile
'Find last column of data - assumes headers in row1
lCol = Range("IV1&quot.End(xlToLeft).Column
'assign value to holding variable
holder = 2
'loop thru used columns, finding the last cell in each
For i = 1 To lCol
lRow = Range(Cells(65536, i), Cells(65536, i)).End(xlUp).Row
If lRow > holder Then
holder = lRow
Else
End If
Next i
LastRow = holder
End Function

If there are any considerations like headers not in row 1 or gaps between the top of the sheet and the start of the data, these can be built in
HTH
Geoff HTH
~Geoff~

 

[GlennUK]

Or you could create a defined name formula.

Do menu command Insert/Name/Define, and type a name of LastRow, and in "Refers To" box type this ...

=GET.DOCUMENT(10)

and press OK.

Now type LastRow in any spare cell on the sheet, and the result will be the row number of the last used row.

Glenn.

 

[xlbo]

Glenn - very nice indeed - can this be used in code ??
get.document(10) doesn't work but is there any syntax to make it work - would be a very nice, neat way of getting the last row
BTW - does this suffer from the same probs as usedrange ??
HTH
~Geoff~

 

[GlennUK]

Hi Geoff,

this is the syntax ...

ret = ExecuteExcel4Macro("GET.DOCUMENT(10)&quot

and it does suffer from the usedrange "last cell used" problem, in that if the last row has been deleted, then the function still counts to the row where data used to be.

Glenn.

 

[xlbo]

Thx Glenn - used in conjunction with:
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count

to reset the usedrange, this works very nicely and doesn't rely on the longest set of data being in any particular column

For the record, in a sub
Sub FindLastRow()
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
LastRow = ExecuteExcel4Macro("GET.DOCUMENT(10)&quot
End Sub

This is about the most concise you can make the finding of the TRUE last used row of a worksheet

For a function, this seems to work well:
Function FindLastRow()
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
FindLastRow = r
End Function
HTH
~Geoff~

 

[GlennUK]

Well done,

that is the most concise method of finding the TRUE last row, so here is a star.

Cheers, Glenn.

 

[GlennUK]

Hi Geoff,

in VBA, you could SpecialCells instead of using that GET.DOCUMENT command. This should work ...


Sub FindLastRow()
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
End Sub

Glenn.

 

[xlbo]

Glenn - yes indeed - and this will allow you to get the full address or column or row - much more useful - have a star yourself, I'm gonna write a FAQ
<g> HTH
~Geoff~

 

[akn846]

I would like to thank everyone for all their input into what must seem quite a simple question.

Many thanks for taking the effort to contribute.

Regards

Andy