VBA to Go to Today in a filtered range

SanjayGMusafir

Well-known Member
Joined
Sep 7, 2018
Messages
1,503
Office Version
  1. 2021
Platform
  1. 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

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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi Sanjay,

If I understand you correctly, your "Dt" column in the table "Banks" is column C on the sheet "Bank"? If so, please try the following to see if it gives you what you want.

VBA Code:
Option Explicit
Sub Sanjay()
    Dim c As Range

    For Each c In Worksheets("Bank").Range("C2", Cells(Rows.Count, "C").End(xlUp))
        If c = Date And c.EntireRow.Hidden = False Then
            c.Select
            Exit For
        End If
    Next c

End Sub
 
Upvote 0
Solution
Hi Sanjay,

If I understand you correctly, your "Dt" column in the table "Banks" is column C on the sheet "Bank"? If so, please try the following to see if it gives you what you want.

VBA Code:
Option Explicit
Sub Sanjay()
    Dim c As Range

    For Each c In Worksheets("Bank").Range("C2", Cells(Rows.Count, "C").End(xlUp))
        If c = Date And c.EntireRow.Hidden = False Then
            c.Select
            Exit For
        End If
    Next c

End Sub
Thanks Kevin

I have tried the above code, it works perfectly fine.

The only concern remains is that I generally look for date ranging between today & today+30 - Just in case there is not entry on current date, it reaches the intended row.

How and where can we modify the above VBA for that.

Thanks again
 
Upvote 0
Thanks Kevin

I have tried the above code, it works perfectly fine.

The only concern remains is that I generally look for date ranging between today & today+30 - Just in case there is not entry on current date, it reaches the intended row.

How and where can we modify the above VBA for that.

Thanks again
Using your idea kevin, I modified the code to -

VBA Code:
Dim x As Range

    For Each x In Worksheets("Bank").Range("C2", Cells(Rows.Count, "C").End(xlUp))
        If x >= Date And x <= Date + 30 And x.EntireRow.Hidden = False Then
            x.Select
            Exit For
        End If
    Next x

It worked.

Just in case you have a better idea, do suggest that.

Thanks again
 
Upvote 0
Using your idea kevin, I modified the code to -

VBA Code:
Dim x As Range

    For Each x In Worksheets("Bank").Range("C2", Cells(Rows.Count, "C").End(xlUp))
        If x >= Date And x <= Date + 30 And x.EntireRow.Hidden = False Then
            x.Select
            Exit For
        End If
    Next x

It worked.

Just in case you have a better idea, do suggest that.

Thanks again
Thanks Sanjay - your amendment is exactly what I would have suggested 🙂
 
Upvote 0

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top