Autofilter Row Count

[James]

Hi, I have the following code, how come I get the value of 1 everytime i do
rng.rows.count (which is wrong)? Im using rng.rows.count in another private
function. Thanks in advance

Public Sub RangeFind()
Dim shtName As String
Dim wShData As Worksheet
shtName = ActiveSheet.Name
Set wShData = ActiveWorkbook.Worksheets(shtName)

With wShData.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1,
..Columns.Count).SpecialCells xlCellTypeVisible)
On Error GoTo 0
End With
End Sub

 

[Dave Peterson]

rng.rows.count will give you the number of rows in the first area of rng (and my
bet is your rng consists of multiple non-contiguous areas).

rng.rows.count is equivalent to rng.areas(1).rows.count

If you want to get the number of rows in the autofilter range (and set a
variable for just the details), you can do something like this that I saved from
a previous post:

With activesheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox "only the headers are visible"
else
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With

 

[James]

Thanks for the reply. I ended up using:
rwcount = .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count -1
Worked great

 

[ryguy7272]

Take a look at this:
http://www.contextures.com/xlautofilter03.html

Regards,
Ryan---