On Nov 9, 4:50*pm, Dave Peterson wrote:
Add
Msgbox R.address
And you'll see a couple of rows in the address.

R.rows.count will return the number of rows in the first area of the range. *And
in your sample code with the sample data, the first area is a single row.

Try this with your same data:

Option Explicit
Sub test()

Dim r As Range

With Sheets("Sheet2")
* * .AutoFilterMode = False
* * .UsedRange.AutoFilter field:=1, Criteria1:="jdjj"
* * .UsedRange.AutoFilter field:=2, Criteria1:="jkkjk"

* * Set r = .UsedRange.SpecialCells(xlCellTypeVisible)
* * MsgBox r.Address

* * .UsedRange.AutoFilter
*End With

End Sub

And you'll see two rows in the address--but only one area. *And that means
you'll see 2 rows in the r.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:

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

signon77wrote:

<<snipped





Hi Dave,

I've just tried writing the same code at home today with the same
problems:

The following is the data in my spreadsheet

first * second
jdjj * *jkkjk
sss * * sdd
sss * * fda
sa * * *fdfd
hah * * klk

The following is the code I'm wrting to test it:

Sub test()

Dim r As Range

With Sheets("Sheet2")
* .AutoFilterMode = False
* .UsedRange.AutoFilter field:=1, Criteria1:="hah"
* .UsedRange.AutoFilter field:=2, Criteria1:="klk"

* Set r = .UsedRange.SpecialCells(xlCellTypeVisible)

* * .UsedRange.AutoFilter
*End With

End Sub

Problem: Even though both the header and one row of data are clearly
visible, range r only ever returns the header row (r.rows.count is
always = 1 instead of 2) .

Any idea what might be causing this problem?

Rob

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

Thanks a lot.

I had no idea that r.rows.count would ever only return data in the
first row because r is actually a range comprised of multiple areas.

This painful lesson has been worth it.

Rob