VBA Autofilter Cell(3).Value2

[GlennL]

Hello,

I am trying to get the value2 of the third row of column6 of a worksheet which has an autofilter applied.

I tried the code below, but I am getting the value of as if there is no autofilter.

Set rng = ws.AutoFilter.Range.columns(6).SpecialCells(xlCellTypeVisible).Cells(3)

Any suggestions?

 

[Alex Blakenburg]

Simplified per @RoryA's suggestion:

Sub GetOccurenceOfVisibleCells_Mod()

Dim ws As Worksheet
Dim rngVis As Range
Dim rCell As Range
Dim iCell As Long, iTgtCell As Long
Dim rTgt As Range

Set ws = ActiveSheet

Set rngVis = ws.AutoFilter.Range.Columns(6).Offset(1).SpecialCells(xlCellTypeVisible)

iTgtCell = 3

For Each rCell In rngVis
    iCell = iCell + 1
    If iCell = iTgtCell Then
        Set rTgt = rCell
        Exit For
    End If
Next rCell

Debug.Print rTgt.Address, rTgt.Value

End Sub

 

[GlennL]

Here's another option you could try (I don't know if there's a more direct way).

It work great thank you!
I was after the 3 data row after the header so I added the offset range

AutoFilter.Range.Columns(6).Offset(1).SpecialCells(xlCellTypeVisible)

from @Alex Blakenburg
If I could add both of these as solutions I would.

 

[Alex Blakenburg]

Thanks for the feedback, it was definitely a collaborative effort

 

[myall_blues]

Your code only works because the column you are filtering on happens to have all the same values in contiguous blocks. Change F3 to 2, reapply the filter for 1, then try your code.

Well there's a trap for young players. Thanks very much for the explanation Rory.