VBA to Go to Today in a filtered range

[SanjayGMusafir]

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

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

 

[kevin9999]

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.

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

 

[SanjayGMusafir]

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.

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

 

[SanjayGMusafir]

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 -

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

 

[kevin9999]

Using your idea kevin, I modified the code to -

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