View Full Version : How to locate nth Visible cell in the filtered columns?
prabhafriend
11-25-2010, 05:42 AM
reconsheet.Columns(1).SpecialCells(xlCellTypeVisible).cells(2).value is not showing the filtered cell's value. How to get that?
Bob Phillips
11-25-2010, 06:04 AM
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
11-25-2010, 06:04 AM
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
11-25-2010, 06:08 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.