Selecting a cell based on counting visible cells from the top

A

Alan

I want to be able to select a cell in column "J" where the row to
select is the 3rd visible row from the top. I want to be able to freeze
the pane at this point. Filtering is employed on this sheet and it is
likely the 3rd visible row down can be any row between 3 and 2400.

Thanks, Alan
 
D

Dave Peterson

Doesn't this make the data act pretty funny when you unfilter the range?

In fact, say you have the same data (xxx) in rows 2:55.

You filter to show yyy.
You apply your freeze panes.

Then you data|Filter|showall.

It might be very difficult to explain what the user should do so that they can
scroll down the worksheet.

I'd just apply the freeze|panes right after the heading row (with no filter
active).

But if you have to, you could just loop through the cells:

Option Explicit
Sub testme()

Dim iRow As Long
Dim myCell As Range

With ActiveSheet
For iRow = 3 To .Rows.Count
If .Cells(iRow, "G").EntireRow.Hidden = False Then
Set myCell = .Cells(iRow, "G")
Exit For
End If
Next iRow
If myCell Is Nothing Then
'do nothing
Else
ActiveWindow.FreezePanes = False
Application.Goto myCell
ActiveWindow.FreezePanes = True
End If
End With

End Sub

There maybe other ways to do this, but I think we'd have to know where your
headers were and what row got the autofilter.
 
Top