Autofilter Row Count
 

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

Autofilter Row Count
 

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 wrote:

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

Autofilter Row Count
 

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


"Dave Peterson" wrote:

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 wrote:

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

Autofilter Row Count
 

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

Regards,
Ryan---

--
RyGuy


"James" wrote:

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


"Dave Peterson" wrote:

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 wrote:

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