Getting no of used rows in a worksheet

[JayTee]

I ended up writing this function In an attempt to get the number of
rows used in a worksheet. I tried using Rows.Count but it did not work
(unless I was doing something stupid).

This function does work, but I can't help wondering wether there is a
more efficient method.

Can anyone help???

----------------------------------------------------------------------------------------------------------------------------------
Public Function GetNoOfRows(Optional ByVal sSheetName As String) As
Long
' gets no of rows by searching for the first non blank row from the
bottom. Returns the row number of the first non blank row from the
bottom of the worksheet
'
Dim ws As Worksheet, saved_ws As Worksheet
Dim lNRows As Long, lRowIdx As Long, i As Long, iColIdx As Integer
Dim fUpdateStatus As Boolean

On Error GoTo Err

fUpdateStatus = Application.ScreenUpdating ' save screen
update status
Application.ScreenUpdating = False
fInsideGetNoOfRows = True

Set saved_ws = ActiveSheet ' save active
worksheet
Set ws = ActiveSheet

If (sSheetName <> Empty) Then Set ws = Worksheets(sSheetName)

lNRows = 0
lRowIdx = 0

For iColIdx = 1 To 256 ' column A to
column IV
ws.Activate
ws.Range(R1C1ToA1(65536, iColIdx)).End(xlUp).Select ' goto last
row and go up to non empty cell
lRowIdx = ActiveCell.Row ' get its row
ws.Range("A1").Select ' goto A1

If (lRowIdx > lNRows) Then lNRows = lRowIdx
Next iColIdx

saved_ws.Activate ' restore active
worksheet
Application.ScreenUpdating = fUpdateStatus ' restore update
status

GetNoOfRows = lNRows
fInsideGetNoOfRows = False
Exit Function

Err:
MsgBox Prompt:="VBA Err " & Chr(34) & Err.Description & Chr(34) &
", getting no of rows.", _
Title:=ActiveWorkbook.Name, Buttons:=vbOKOnly +
vbApplicationModal + vbCritical

GetNoOfRows = 0
fInsideGetNoOfRows = False
End Function

 

[Bob Phillips]

ws.UsedRange.Rows.Count

will return the actual number of rows, but if there is a gap at the top,
that gets ignored. You can add that back in with

ws.UsedRange.Rows.Count + ws.UsedRange.Row -1

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[JayTee]

Thanks Bob,
I had previously tried to use UsedRange.Rows.Count however it seemed to
pick up rows that had previously been formatted, even though they had
no values. I will try working around it, thanks very much for your
input.
If you do celebrate Xmas, wishing you a Merry Xmas and a Happy New
Year.

Regards
JT

 

[Bob Phillips]

JT,

Thanks for the greetings, and they are warmly returned.

As to UsedRange, Debra Dalgleish has a page on that problem
http://www.contextures.com/xlfaqApp.html#Unused

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)