Hello,

Whenevr I try to get the number of rows in a worksheet, it returns 65526
which is actually the maximum number of possible in a sheet. If I have used
only 7 rows in a sheet, I want to get the answer as 7. How can I get?

Thank you

To use a worksheet function, if you have a column that never contains embedded
blank cells, you can write something like =COUNTA(A:A). Or, if you know which
column will be the longest, say C, you can use an array formula something like
this

=MAX(IF(ISBLANK(C1:C2000),0,ROW(C1:C2000)))

Enter this with CTRL+SHIFT+ENTER. Change the number 2000, if necessary, to
some number that you know is too high.



On Sun, 30 Jan 2005 20:07:31 -0800, "gana"
wrote:

Hello,

Whenevr I try to get the number of rows in a worksheet, it returns 65526
which is actually the maximum number of possible in a sheet. If I have used
only 7 rows in a sheet, I want to get the answer as 7. How can I get?

Thank you

"Myrna Larson" wrote...
To use a worksheet function, if you have a column that never contains
embedded blank cells, you can write something like =COUNTA(A:A). Or,
if you know which column will be the longest, say C, you can use an
array formula something like this

=MAX(IF(ISBLANK(C1:C2000),0,ROW(C1:C2000)))

Enter this with CTRL+SHIFT+ENTER. Change the number 2000, if necessary, to
some number that you know is too high.
....

More efficient to use the array formula

=MATCH(2,1/(1-ISBLANK(C1:C2000)))

Hello,
Thanks for the reply. But I'm trying to do this in excel automation.
Whenever I use the property count..it returns the maximum number of rows. How
to get the actual number of rows usd in that perticular sheet.

Thank you.

"gana" wrote:

Hello,

Whenevr I try to get the number of rows in a worksheet, it returns 65526
which is actually the maximum number of possible in a sheet. If I have used
only 7 rows in a sheet, I want to get the answer as 7. How can I get?

Thank you

Hi Harlan, how about the following with no need to array enter I think?

=LOOKUP(2,1/(C1:C2000<""),ROW(C1:C2000))

Are there any instances where the MATCH function would catch it that the
LOOKUP wouldn't. I've only just started playing with this use of the
function based on some posts of Aladin's in Mr Excel so just curious.

Regards
Ken.............

"Harlan Grove" wrote:

"Myrna Larson" wrote...
To use a worksheet function, if you have a column that never contains
embedded blank cells, you can write something like =COUNTA(A:A). Or,
if you know which column will be the longest, say C, you can use an
array formula something like this

=MAX(IF(ISBLANK(C1:C2000),0,ROW(C1:C2000)))

Enter this with CTRL+SHIFT+ENTER. Change the number 2000, if necessary, to
some number that you know is too high.
....

More efficient to use the array formula

=MATCH(2,1/(1-ISBLANK(C1:C2000)))

LastRw = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

LastRw = Sht1.Cells(Rows.Count, "A").End(xlUp).Row

LastRw = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

First 2 based on a particular column, and last one based on usedrange.

Regards
Ken................

"gana" wrote:

Hello,
Thanks for the reply. But I'm trying to do this in excel automation.
Whenever I use the property count..it returns the maximum number of rows. How
to get the actual number of rows usd in that perticular sheet.

Thank you.

"gana" wrote:

Hello,

Whenevr I try to get the number of rows in a worksheet, it returns 65526
which is actually the maximum number of possible in a sheet. If I have used
only 7 rows in a sheet, I want to get the answer as 7. How can I get?

Thank you

Ken Wright wrote...
Hi Harlan, how about the following with no need to array enter I
think?

=LOOKUP(2,1/(C1:C2000<""),ROW(C1:C2000))

Are there any instances where the MATCH function would catch it that
the
LOOKUP wouldn't. I've only just started playing with this use of the
function based on some posts of Aladin's in Mr Excel so just curious.
....
"Harlan Grove" wrote:
....
=MATCH(2,1/(1-ISBLANK(C1:C2000)))

Aside from the brevity of the MATCH formula, it's processing a single
array, whereas the LOOKUP formula requires two derived arrays. That
can't improve recalc speed. Then there's the all too common equivalence
drawn between a cell evaluation to "" and being blank. If blank or
pseudoblank ("") should be considered equivalent, then

=MATCH(2,1/(C1:C2000<""))

Otherwise, ISBLANK is necessary. While I try to avoid array formulas
whenever I can, there are times when they provide better solutions.
IMO, this is one of those cases.

Cheers Harlan, much appreciated.

--
Regards
Ken.......................

"Harlan Grove" wrote in message
oups.com...
Ken Wright wrote...
Hi Harlan, how about the following with no need to array enter I
think?

=LOOKUP(2,1/(C1:C2000<""),ROW(C1:C2000))

Are there any instances where the MATCH function would catch it that
the
LOOKUP wouldn't. I've only just started playing with this use of the
function based on some posts of Aladin's in Mr Excel so just curious.
...
"Harlan Grove" wrote:
...
=MATCH(2,1/(1-ISBLANK(C1:C2000)))

Aside from the brevity of the MATCH formula, it's processing a single
array, whereas the LOOKUP formula requires two derived arrays. That
can't improve recalc speed. Then there's the all too common equivalence
drawn between a cell evaluation to "" and being blank. If blank or
pseudoblank ("") should be considered equivalent, then

=MATCH(2,1/(C1:C2000<""))

Otherwise, ISBLANK is necessary. While I try to avoid array formulas
whenever I can, there are times when they provide better solutions.
IMO, this is one of those cases.

Which may or may not be correct, depending upon what Excel thinks is the "used
range".

I would make sure the used range had been reset before depending upon Excel's
estimation.

Or use this UDF in combination with a macro.

Function RangeToUse(anySheet As Worksheet) As Range
Dim i As Integer, C As Integer, R As Integer
With anySheet.UsedRange
i = .Cells(.Cells.Count).Column + 1
For C = i To 1 Step -1
If Application.CountA(anySheet.Columns(C)) 0 _
Then Exit For
Next
i = .Cells(.Cells.Count).Row + 1
For R = i To 1 Step -1
If Application.CountA(anySheet.Rows(R)) 0 Then _
Exit For
Next
End With
With anySheet
Set RangeToUse = .Range(.Cells(1, 1), .Cells(R, C))
End With
End Function

Sub UsedRangePick()
Dim tempRange As Range
Set tempRange = RangeToUse(ActiveSheet)
tempRange.Select
mycount = Selection.Rows.Count
MsgBox "This selection contains " & mycount _
& " row(s)", vbInformation, "Count Rows"
End Sub


Gord Dibben Excel MVP

On Mon, 31 Jan 2005 04:47:06 -0800, "Ken Wright"
wrote:

LastRw = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

First 2 based on a particular column, and last one based on usedrange.

Gord Dibben wrote...
....
Or use this UDF in combination with a macro.

Function RangeToUse(anySheet As Worksheet) As Range
Dim i As Integer, C As Integer, R As Integer
With anySheet.UsedRange
i = .Cells(.Cells.Count).Column + 1
For C = i To 1 Step -1
If Application.CountA(anySheet.Columns(C)) 0 Then Exit For
Next
i = .Cells(.Cells.Count).Row + 1
For R = i To 1 Step -1
If Application.CountA(anySheet.Rows(R)) 0 Then Exit For
Next
End With
With anySheet
Set RangeToUse = .Range(.Cells(1, 1), .Cells(R, C))
End With
End Function
....

Repeatedly calling COUNTA on worksheet ranges isn't a recipe for nimble
VBA procedures. Also, if the goal were locating the last row with
nonblank cells, there's no reason to reduce the number of columns.
Finally, this might be useful as a UDF, so generalize it.


Function bmr(Optional x As Variant) As Long
Dim ur As Range, r As Range

If IsMissing(x) Then
Set ur = ActiveSheet.UsedRange
ElseIf TypeOf x Is Worksheet Then
Set ur = x.UsedRange
ElseIf TypeOf x Is Range Then
Set ur = x.Worksheet.UsedRange
Else
On Error Resume Next
bmr = -1 'return -1 for invalid args
Set ur = Evaluate(x).Worksheet.UsedRange
If Err.Number < 0 Then Exit Function
On Error GoTo 0
End If

Set r = ur.Cells(ur.Cells.Count)

Do While r.Row 1 And r.Formula = "" And r.End(xlToLeft).Formula =
""
Set r = r.Offset(-1, 0)
Loop

'return 0 if used range is A1 only and A1 is blank
If r.Row 1 Or r.Formula < "" Or r.End(xlToLeft).Formula < "" Then
_
bmr = r.Row
End Function


And similarly for the rightmost column.


Function rmc(Optional x As Variant) As Long
Dim ur As Range, r As Range

If IsMissing(x) Then
Set ur = ActiveSheet.UsedRange
ElseIf TypeOf x Is Worksheet Then
Set ur = x.UsedRange
ElseIf TypeOf x Is Range Then
Set ur = x.Worksheet.UsedRange
Else
On Error Resume Next
rmc = -1 'return -1 for invalid args
Set ur = Evaluate(x).Worksheet.UsedRange
If Err.Number < 0 Then Exit Function
On Error GoTo 0
End If

Set r = ur.Cells(ur.Cells.Count)

Do While r.Column 1 And r.Formula = "" And r.End(xlUp).Formula = ""
Set r = r.Offset(0, -1)
Loop

'return 0 if used range is A1 only and A1 is blank
If r.Column 1 Or r.Formula < "" Or r.End(xlUp).Formula < "" Then
_
rmc = r.Column
End Function


And generate the range from A1 to the last cell in which there's a
nonblank cell in the same row and column using the previous functions.


Function nbr(Optional x As Variant) As Range
Dim ws As Worksheet
Dim r As Long, c As Long

If IsMissing(x) Then
Set ws = ActiveSheet
ElseIf TypeOf x Is Worksheet Then
Set ws = x
ElseIf TypeOf x Is Range Then
Set ws = x.Worksheet
Else
On Error Resume Next
'return Nothing for invalid args
Set ws = Evaluate(x).Worksheet
If Err.Number < 0 Then Exit Function
On Error GoTo 0
End If

r = bmr(ws)
c = rmc(ws)

If r 0 And c 0 Then
Set nbr = ws.Range("A1").Resize(r, c)
Else
Set nbr = ws.Range("A1")
End If
End Function