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

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

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
>

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