Selecting top cell after auto filter procedure

  • Thread starter Thread starter rob nobel
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top