SpecialCells(xlCellTypeVisible) driving me nuts

[signon77]

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

 

[Peter T]

Try this -

Dim r as Range, ar as Range

' code

For Each ar In r.Areas
With ar
Debug.Print .Rows.Count, .Address
End With
Next

Regards,
Peter T

 

[JLGWhiz]

See what you get if you use:

Set r = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)

 

[Dave Peterson]

Check your other thread, too.

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

 

[signon77]

Check your other thread, too.

















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Guys,

What can I say but thank you?

Who would have thought that range: "r" actually had multiple ranges
known as areas. This painful learning experience has been worth it. I
work at an investment bank in London supporting Middle Office
spreadsheets. Some of them are so bad and slow I silently despair. You
guys helping me like this means I don't have to write a relatively
slow "for loop" to get round the issue.

Thanks again,

Rob