Autofilter not working with multiple field selections

[signon77]

The following code returns only one row of data after the header.

However range: rngColumnsToPopulate seems to only see the header row
not the row beneath it. Anyone know what might be wrong. It's the same
code I've used in the past when only one field has been used for
filtering criteria. Now I'm using 3 fields the code only seems to see
the header row.

With ws

.UsedRange.AutoFilter field:=colSource, Criteria1:="AED",
VisibleDropDown:=False
.UsedRange.AutoFilter field:=colMemo, Criteria1:="=",
VisibleDropDown:=False
.UsedRange.AutoFilter field:=colActionType, Criteria1:="=",
VisibleDropDown:=False

Set rngColumnsToPopulate
= .UsedRange.SpecialCells(xlCellTypeVisible, True)

End With

 

[Dave Peterson]

My guess is that you don't have blank cells in the colmemo and colactiontype
fields -- for the rows that have AED in then colsource field.

Or maybe the range is already filtered and hiding the rows you expected to
see???

With ws
.AutoFilterMode = False '<-- added to remove the autofilter.
.UsedRange.AutoFilter .......


ps. I'm not sure what the True does in .specialcells() portion, either. I'd
drop it.

 

[signon77]

My guess is that you don't have blank cells in the colmemo and colactiontype
fields -- for the rows that have AED in then colsource field.

Or maybe the range is already filtered and hiding the rows you expected to
see???

With ws
.AutoFilterMode = False '<-- added to remove the autofilter.
.UsedRange.AutoFilter .......

ps. I'm not sure what the True does in .specialcells() portion, either. I'd
drop it.



signon77wrote:

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]

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

 

[signon77]

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



















--

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