Selecting top cell after auto filter procedure

R

rob nobel

After running an auto filter procedure in VBA....

1. What is the best method to activate the first row in the visible rows?
2. How can the procedure make sure that as many of the visible rows are
visable without having to manually scroll up?

Rob
 
D

Dave Peterson

Manually???

I just click on the header cell for that column and hit the down arrow.

Code:
Option Explicit
Sub testme()

Dim myRngF As Range

Set myRngF = Nothing
On Error Resume Next
With ActiveSheet.AutoFilter.Range
Set myRngF = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.Cells.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If myRngF Is Nothing Then
MsgBox "No cell to select"
Else
myRngF.Areas(1).Cells(1, 1).Select
End If

End Sub
 
D

Dave Peterson

And to make sure you're at the top of the filter:

instead of the .select statement:

Application.goto myrngf.areas(1).cells(1,1), scroll:=true
 
R

rob nobel

Thanks Dave. Not manually as I want it to happen in one action when the
user clicks a button that does the filtering. (Also, the header row and
above are locked and not selectable)
You're code will do just fine.
Rob
 
Top