By VBA , how to select the first visible cell (of first Visible row ) in filtered wor

[Amolin]

By VBA , how to select the first visible cell (of first Visible row ) i
filtered worksheet?

Thank you

 

[BrianB]

Code

 

[Amolin]

when I use the following code to show the fifth cell in the secon
visible row, it still show the value in phisical "E2" why...

How to show the value the 5th cell in second Visible row?

Thank you..


Sub sss()
MsgBo
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Range("E2").Value
End Su

 

[Myrna Larson]

I believe that Range("E2") is calculated this way: get the upper left cell
from the visible cells, start from there and go PHYSICALLY 1 row down and 4
columns right WITHOUT CONSIDERING whether all intervening rows and columns are
visible. Since the 1st visible cell is probably the 1st cell of the header
row, that's A1. 1 row down and 4 columns right is E5. I think you have to
examine this range, area by area, counting the rows in each area until you get
to the row you want. Assuming all columns are visible, that means code like
this:

Sub Test()
Set Rng = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)
TargetRow = 2 + 1 'assume header row is visible, so add 1 to exclude it
PrevRows = 0
For Each A In Rng.Areas
N = A.Rows.Count
If PrevRows + N >= TargetRow Then 'it's in this block
MsgBox A.Cells(TargetRow - PrevRows, 5).Value
Exit For
End If
PrevRows = PrevRows + N 'update count of rows in higher blocks
Next a
End Sub

 

[Dave Peterson]

Another way:

Option Explicit
Sub testme()

Dim rngV As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Cells.Count = 1 Then
MsgBox "no rows shown"
Exit Sub
End If
Set rngV = .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
MsgBox rngV.Cells(1, 1).Offset(0, 4).Address
End With
End With

End Sub

 

[gvg]

Code
-------------------

Application.Goto _
reference:=ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Range("A1"), _
Scroll:=True

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


--
/QUOTE]

You get same effect with
Cells(1, 1).Select