selecting top left cel in a filtered list after scrolling down

[littlepete]

hello all

this is NOT the question:
how do I select the first visible cell in a filtered list ? that I do know.

but:
how do I select that first visible cell on screen after scrolling down in a filtered list?
which vba line can fix that ?

thank you !!!

 

[jasonb75]

You can get the address of that cell with

Debug.Print ActiveWindow.VisibleRange.Cells(1, 1).Address

although vba does not detect scrolling so there would be no way of automating the selection.

 

[littlepete]

You can get the address of that cell with

Debug.Print ActiveWindow.VisibleRange.Cells(1, 1).Address

although vba does not detect scrolling so there would be no way of automating the selection.

ohhhh !!!

and hello

I didnt know that ... but: it's my vba that chronologically puts the first item after or on today at the top...
that works well... so could there be a way to select the cell in column A of that first row after this:

Sub scrolltoday()
    Dim Rng As Range
    Dim c As Range
    Dim firstrow As Long
    Dim lastrow As Long
    Dim foundrow As Long
    Dim searchdate As String
    firstrow = 5 'startrow for search - first cell under title
    searchdate = Format(Date, "mmdd") & "0"
    lastrow = Range("cd" & Rows.Count).End(xlUp).Row
    Set Rng = Range("cd" & firstrow & ":cd" & lastrow)
    foundrow = 0
    For Each c In Rng
        If c.Value > searchdate And foundrow = 0 Then foundrow = c.Row
    Next
    If foundrow > 0 Then ActiveWindow.ScrollRow = Rng(foundrow + 1 - firstrow).Row
    tooneersterij
End Sub

and, the "showfirstrow" macro (tooneersterij) is this:

Sub tooneersterij()
    If ActiveSheet.FilterMode = True Then
        ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
    Else
        Range("a5").Select
    End If
Calculate
End Sub

so, is there a way ? ..
hoping !

 

[jasonb75]

As you're using vba to scroll it should be simple, although I don't have a suitable workbook to hand to test the theory so it may involve a bit of trial and error.
Try changing the selection line in tooneersterij to

ActiveWindow.VisibleRange.Cells(1, 1).Select

and see what happens.

 

[littlepete]

ActiveWindow.VisibleRange.Cells(1, 1).Select

wowww !!! that works, and it's really not that difficult !!! but i never tried visiblerange...

so, thank you for the help !!!
and always welcome to try to "fix" the other questions !!! thank you !!!