SpecialCells(xlCellTypeVisible) driving me nuts
 

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

SpecialCells(xlCellTypeVisible) driving me nuts
 

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


"signon77" wrote in message
...

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

SpecialCells(xlCellTypeVisible) driving me nuts
 

r.rows.count only counts the rows in the first area of r.

Try

Code:
--------------------
Sub test()
Dim r As Range
Dim nRow As Long

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

Set r = .UsedRange.SpecialCells(xlCellTypeVisible)

.UsedRange.AutoFilter
nRow = Intersect(r(1).EntireColumn, r).Count
MsgBox "nRow = " & nRow
End With
End Sub
--------------------


--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27214

SpecialCells(xlCellTypeVisible) driving me nuts
 

See what you get if you use:

Set r = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)

"signon77" wrote:


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

SpecialCells(xlCellTypeVisible) driving me nuts
 

Check your other thread, too.

signon77 wrote:

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

SpecialCells(xlCellTypeVisible) driving me nuts
 

On Nov 9, 6:06*pm, Dave Peterson wrote:
Check your other thread, too.





signon77 wrote:

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 -

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