SanjayGMusafir
Well-known Member
- Joined
- Sep 7, 2018
- Messages
- 1,503
- Office Version
- 2021
- Platform
- MacOS
Hi Experts
I use following code to jump to today in a filtered range.
It works well when table is sorted by date first and then by any other field.
But challenge arises when table is sorted first by any other field and then by date. In such cases present date(s) can be around 1000+ rows apart.
Can there be some way that VBA finds current date in the filtered data/range only.
Please help.
Thanks a lot
I use following code to jump to today in a filtered range.
It works well when table is sorted by date first and then by any other field.
But challenge arises when table is sorted first by any other field and then by date. In such cases present date(s) can be around 1000+ rows apart.
Can there be some way that VBA finds current date in the filtered data/range only.
Please help.
Thanks a lot
VBA Code:
Sub BankJumpToday()
'
' BankJumpToday Macro
'
'
'To shift sheets for Call GoHome to work properly
Sheets("Client").Select
'BankJumpToday Starts here
Sheets("Bank").Select
Call GoHome
Application.ScreenUpdating = True
Range("Banks[[#Headers],[Dt]]").Select
'ActiveSheet.AutoFilterMode = False
'To Go to Today
Dim ACell As String, Lr As Long, BCol As Long
Lr = Range("C" & Rows.Count).End(xlUp).Row
BCol = Sheets("Bank").ListObjects("Banks").ListColumns("Dt").DataBodyRange.Column
ACell = Evaluate("ADDRESS(MATCH(1,INDEX((C1:C" & Lr & "> TODAY() - 1 )*(C1:C" & Lr & "<TODAY() + 31), 0, 1),0)," & BCol & ",4)")
Range(ACell).Select
'ActiveSheet.ListObjects("Banks").ShowAutoFilterDropDown = False
End Sub