How can i know how many rows in a worksheet?

[zhoujinting]

hi,everyone
i have a problem.How to make the fcnction know how many rows in a
worksheet?
Can anyone help me?

 

[Jim]

There are always 65,536 rows in each worksheet.

If you are referring to the used range, that is a different matter. You
could use =COUNTA(A2:A65536) which will count the number of cells in the
range with something in them. If you want to find the number of rows where
one cell within many in a row have an entry, you will need to resort to VBA.

 

[Cecilia Reyes]

i have a similar problem, since i have to count how many
rows are in a worksheet *with anything* in them and
include the rows that have *nothing* and are in between
the other occupied rows. the thing is that i have to
delete the rows in blank and only leave the occupied rows
here´s what i do but it only works if there´s just one row
unused:

ActiveCell.CurrentRegion.Select
a = Selection.Rows.Count

Do While Cells(a + 2, 3) <> ""
ActiveCell.CurrentRegion.Select
a = Selection.Rows.Count

If (Cells(a + 1, 3) = "") Then
Cells(a + 1, 3).Select
Selection.EntireRow.Delete shift:=xlUp

End If

Loop

i think the trick is in the condition, what do you think?

 

[zhoujinting]

I want to find the number of rows where
one cell within many in a row have an entry.What can i do?
can you show me the vba code?
thanks.

 

[Paul]

hi,everyone
i have a problem.How to make the fcnction know how many rows in a
worksheet?
Can anyone help me?

This array-formula (needs to be entered with Ctrl+Shift+Enter rather than
just Enter) will return the row number of the last row within the range
A1:C100 that contains anything.
=MAX((A1:C100<>"")*ROW(A1:A100))
You can adjust the ranges to suit your situation. It isn't a general answer
to your problem as it doesn't work over the entire worksheet, but often that
isn't necessary.

 

[zhoujinting]

Yes.It works.though i don't quite understand the formula.
thank you very much.