Range.Rows property with autofilter applied

[Dmitry_D_Onishchenko]

Hi MrExcel, I need your help.

I have an autofilter applied to a range resulting in non-continuous set of rows visible (see snapshot).

Assume I want to access the number of the first visible non-header row (which is number 7 in this case). When I run

Dim rng As Range
Set rng = Ws.Range("B1:B100").SpecialCells(xlCellTypeVisible)
Debug.Print "XXX: " & rng.Address
Debug.Print "ZZZ: " & rng.Rows(2).row

, I get

XXX: $B$1,$B$7,$B$11,$B$25:$B$100
ZZZ: 2

which exactly matches my expectations in line XXX but does not in line ZZZ: I would expect rng.Rows(2) to return the ordinal row number (i.e., 7 and not 2).

What do I miss here? Please advise.

Thanks in advance,
Dmitry

 

[MARK858]

What do you get with

Debug.Print "ZZZ: " & ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(12).Cells(1, 1).Row

 

[Marcelo Branco]

Maybe something like this...

Sub aTest()
    Dim ws As Worksheet, rng As Range
      
    Set ws = Worksheets("Sheet1")
    With ws
        With .AutoFilter.Range.Columns(2)
            'This excludes header's row
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
        End With
        
        If Not rng Is Nothing Then
            MsgBox rng.Address
            MsgBox rng.Areas(1).Cells(1).Row
        End If
    End With
End Sub

Hope this helps

M.

 

[Dmitry_D_Onishchenko]

What do you get with

Debug.Print "ZZZ: " & ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(12).Cells(1, 1).Row

Mark,

With this I get 7 as required! Impressive. May I ask if it's generalizable? I mean, what if I want to get the number of n-th visible row? I can't figure out how to increment the Offset() and Cells() arguments appropriately - I still get increments to 7 instead of 11.

 

[RoryA]

For a generalised approach I think you'll need to use a For Each loop and use a counter.

 

[Dmitry_D_Onishchenko]

For a generalised approach I think you'll need to use a For Each loop and use a counter.

Yep, I just had a hope there is an elegant (or even built-in) approach which avoids constructing a loop. Well, a counter is not a big problem either (with @Marcelo Branco 's reply above). Thanks!

 

[Dmitry_D_Onishchenko]

Sub aTest()
' ...
End Sub

Hope this helps

M.

Marcelo, that's huge! Many thanks! Your solution is easily generalizable for number of continuous areas >1, which is crucial for me.

 

[Marcelo Branco]

You are welcome.
Remark: as Rory said to a generalized solution maybe you need to loop for each Area and for each Row inside the area.

M.