How to locate nth Visible cell in the filtered columns?

[prabhafriend]

reconsheet.Columns(1).SpecialCells(xlCellTypeVisible).cells(2).value is not showing the filtered cell's value. How to get that?

[Bob Phillips]

Dim rng As Range
Dim rea As Range
Dim cell As Range
Dim i As Long
Set rng = ActiveSheet.Columns(1).SpecialCells(xlCellTypeVisible)
For Each rea In rng.Areas

For Each cell In rea

i = i + 1
If i = 3 Then

MsgBox cell.Value
Exit Sub
End If
Next cell
Next rea

[Bob Phillips]

Dim rng As Range
Dim rea As Range
Dim cell As Range
Dim i As Long
Set rng = ActiveSheet.Columns(1).SpecialCells(xlCellTypeVisible)
For Each rea In rng.Areas

For Each cell In rea

i = i + 1
If i = 3 Then

MsgBox cell.Value
Exit Sub
End If
Next cell
Next rea

[Bob Phillips]

ANother way



Dim rng As Range
Dim rea As Range
Dim i As Long
Dim j As Long

Set rng = ActiveSheet.Columns(1).SpecialCells(xlCellTypeVisible)
For Each rea In rng.Areas

j = rea.Cells.Count
If i + j >= 3 Then

MsgBox rea.Cells(3 - i, 1).Value
Exit For
Else

i = i + j
End If
Next rea