Select 2nd value in filtered range

[dotsent]

I'm looking to select the 2nd visible value from autofiltered range.

I have a table MyTable and looking to get the value from column G (also named as MyRange). The code snippet below works fine with autofiltered range, but all I can do is locate the first visible value in a column G - .Offset(n, 1) will not make any difference, I'm always getting the first value.

Sub Select()
  
    With ActiveSheet.ListObjects("MyTable").Range    
    Range("A1").Value = Range("G" & .Offset(1, 1).SpecialCells(xlCellTypeVisible).Row).Value
    End With

End Sub

Second simple code works fine to locate the 2nd value in a range, however ignores the filtering results.

Sub Select2()

Range("A1").Value = Range("MyRange").Cells(2, 1).Value

End Sub

Am I missing the proper parameters for Offset function in the 1st code? I could probably use helper range, copy the filtered range there and then run the second code which works with unfiltered range, however I feel there has to be a nicer way to do it. Alternative to a 2nd value could also be the last visible value in range.

 

[JoeMo]

Perhaps your visible cells are not all contiguous, in which case the second visible cell may be in a different area than the first visible cell.

Here's an example table (shown filtered on col G with criteria "data73") and some code to call out the addresses of the visible cells in col G.

Excel Workbook
	D	E	F	G	H	I
1	hdr1	hdr2	hdr3	hdr4	hdr5	hdr6
3	data43	data53	data63	data73	data83	data93
8	data48	data58	data68	data73	data88	data98
13	data413	data513	data613	data73	data813	data913
Sheet1

Sub test()
Dim LO As ListObject, i As Long, c As Range
Set LO = ActiveSheet.ListObjects("Table1")
With LO.DataBodyRange
    .AutoFilter field:=4, Criteria1:="data73"
    On Error Resume Next
    With .SpecialCells(xlCellTypeVisible)
        For i = 1 To .Areas.Count
            For Each c In .Areas(i).Columns(4) 'change column index to suit your table
                MsgBox c.Address
            Next c
        Next i
    End With
    On Error GoTo 0
End With
End Sub

 

[Peter_SSs]

Does this do what you want?

Range("A1").Value = Evaluate(Replace("=INDEX(#,MATCH(2,SUBTOTAL(3,OFFSET(#,,,ROW(#)-ROW(INDEX(#,1))+1)),0))", "#", Range("myRange").Address))

 

[dotsent]

Thank you both!

JoeMo, that is correct, after filtering, visible rows often are not next to each other (in terms of native Excel row numbers). I was hoping there's a way Offset or Rows function could take this into account, but as I can see, it's not as simple as that.

Peter, your code worked great. Unfortunately still a little difficult for me to fully understand, but it is functional and seems I can adjust the 2nd value into something else just by adjusting the ...MATCH(n... parameter?

 

[Peter_SSs]

Thank you both!

Peter, your code worked great. Unfortunately still a little difficult for me to fully understand, but it is functional and seems I can adjust the 2nd value into something else just by adjusting the ...MATCH(n... parameter?

You're welcome.

.. and, yes, changing that n would allow you to find the 3rd, 1st, 9th etc.

 

[DanteAmor]

Try this

Sub tst()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="some"
    Range("A1").Value = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, "G").Value
End Sub

 

[Peter_SSs]

Try this

Sub tst()
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="some"
    Range("A1").Value = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, "G").Value
End Sub

Hi Dante

What range does your Table1 occupy?

Also, given a worksheet can have a standard AutoFilter range as well as a ListObject filter, that code could put a value from the standard AutoFilter range into A1 rather than a value from the ListObject.

In any case, won't that code only work reliably for finding the first visible cell? The original question was to identify the second visible cell.

 

[DanteAmor]

Hi Peter,
First, I apologize for delivering a solution that doesn't work.
Second, thanks Peter for your comments, you're right, there may be a standard autofilter and the autofilter of the table.
Finally, if you allow me, I would like to add a couple of solutions, one for the table "MyTable" and one for the named range "myRange", both consider that the table has headings as shown in the following image:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:37.07px;" /><col style="width:37.07px;" /><col style="width:37.07px;" /><col style="width:37.07px;" /><col style="width:37.07px;" /><col style="width:37.07px;" /><col style="width:37.07px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >G8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffc000; ">A</td><td style="background-color:#ffc000; ">B</td><td style="background-color:#ffc000; ">C</td><td style="background-color:#ffc000; ">D</td><td style="background-color:#ffc000; ">E</td><td style="background-color:#ffc000; ">F</td><td style="background-color:#ffc000; ">G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >some</td><td >B6</td><td >C6</td><td >D6</td><td >E6</td><td >F6</td><td >G6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >some</td><td >B8</td><td >C8</td><td >D8</td><td >E8</td><td >F8</td><td >G8</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >some</td><td >B10</td><td >C10</td><td >D10</td><td >E10</td><td >F10</td><td >G10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >some</td><td >B12</td><td >C12</td><td >D12</td><td >E12</td><td >F12</td><td >G12</td></tr></table>

Sub tst1()
  Dim r As Range, n As Long
  For Each r In ActiveSheet.[COLOR=#0000ff]ListObjects("MyTable")[/COLOR].DataBodyRange.SpecialCells(xlCellTypeVisible).Rows
    n = n + 1
    If n = 2 Then
      [A1] = r.Cells(1, "G")
      Exit For
    End If
  Next
End Sub

-------------------------

Sub tst2()
  Dim r As Range, n As Long
  For Each r In ActiveSheet.[COLOR=#0000ff]Range("myRange")[/COLOR].Offset(1).SpecialCells(xlCellTypeVisible).Rows
    n = n + 1
    If n = 2 Then
      [A1] = r.Cells(1, 1)
      Exit For
    End If
  Next
End Sub