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?

 

[myall_blues]

I hope I've understood your question correctly. If so you can put your visible cells into an array and then do with them what you will.

For example, with the following sheet:

Book1
	A	B	C	D	E	F
1	Yellow	Green	Blue	Pink	Black	White
2						1
3						1
4	A	B	C	D	E	1
5						1
6						1
7						1
8						1
9						1
10						1
11						1
12						1
13						1
14						1
15						2
16						2
17	Z	Y	X	W	V	2
18						2
19						2
20						2
Sheet1

If you autofilter this range and filter by column White=1, the code below returns A,B,C,D,E,1
If you filter by White=2, code returns Z,Y,X,W,V,2.

Sub test()
    Dim i As Long
    Dim arr As Variant
    arr = Range("a2:f20").SpecialCells(xlCellTypeVisible).Value2
    For i = LBound(arr, 2) To UBound(arr, 2)
        Debug.Print arr(3, i)
    Next i
End Sub

 

[Fluff]

That code will only return the 1st visible row, whereas the OP is trying to get the value of the 3rd visible row.

 

[myall_blues]

That code will only return the 1st visible row, whereas the OP is trying to get the value of the 3rd visible row.

No - it returns 3rd visible row.

 

[RoryA]

Assigning the Value property to an array only works for contiguous ranges. If you use specialcells, you will only get the values from the first visible area.

 

[myall_blues]

Assigning the Value property to an array only works for contiguous ranges. If you use specialcells, you will only get the values from the first visible area.

I don't understand your explanation Rory. You guys are better at this than me, but my interpretation was OP wanted values from third visible row.

Then if I have this:

Code returns A, B, C, D, E, 1

and if I have this:

code returns Z, Y, X, W, V, 2.

Is that not what was asked for?

 

[RoryA]

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.

 

[Alex Blakenburg]

This is a bit longwinded but should work.
Might need some additional checks for if there are less than 3 cells visible.

Sub GetOccurenceOfVisibleCells()

Dim ws As Worksheet
Dim rngVis As Range
Dim rArea 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 rArea In rngVis.Areas
    For Each rCell In rArea
        iCell = iCell + 1
        If iCell = iTgtCell Then
            Set rTgt = rCell
            Exit For
        End If
    Next rCell
    If iCell = iTgtCell Then Exit For
Next rArea

Debug.Print rTgt.Address, rTgt.Value

End Sub

 

[RoryA]

You don't need to loop areas and then cells with a for each loop - just the Cells will do.

 

[FormR]

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

Sub M()
Dim a As Range, rng As Range, RowNum As Long
RowNum = 3 'Row to find
For Each a In Columns(6).SpecialCells(xlCellTypeVisible).Areas
    If a.Rows.Count >= RowNum Then
        Set rng = a.Cells(RowNum)
        Exit For
    Else
        RowNum = RowNum - a.Rows.Count
    End If
Next a
MsgBox rng.Address
End Sub