Results 1 to 8 of 8

Thread: Scroll to last cell with date

  1. #1
    Board Regular sharky12345's Avatar
    Join Date
    Aug 2010
    Posts
    3,239
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Scroll to last cell with date

    I have a series of dates in column E and I need to scroll to the last cell which has today as the current date.

    If it makes a difference, the cells are formatted as "dd/mm/yyyy"

    Anyone?
    There isn't a day go by without me learning something new about VBA!

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,229
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Scroll to last cell with date

    Maybe...

    Code:
    Sub findlastdate()
    Application.Goto Columns(5).Find(CDate(Date), , xlValues, , xlByRows, xlPrevious), True
    End Sub
    Last edited by MARK858; Oct 9th, 2019 at 04:59 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular sharky12345's Avatar
    Join Date
    Aug 2010
    Posts
    3,239
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Scroll to last cell with date

    Cheers Mark - however, that gives me a run time error 5, Invalid procedure call or argument......

    Any ideas?
    There isn't a day go by without me learning something new about VBA!

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,884
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Scroll to last cell with date

    Try this:
    Since you say Todays Date is in last cell with data in column E.
    Code:
    Sub Goto_Today()
    'Modified 10/9/2019 4:02:05 AM  EDT
    Application.ScreenUpdating = False
    Dim i As Long
    Dim j As Long
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "E").End(xlUp).Row
    Application.Goto Reference:=Range("E" & Lastrow), Scroll:=True
            With ActiveWindow
                i = .VisibleRange.Rows.Count / 2
                j = .VisibleRange.Columns.Count / 2
                .SmallScroll Up:=i, ToLeft:=j
            End With
    Application.ScreenUpdating = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,229
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Scroll to last cell with date

    Quote Originally Posted by sharky12345 View Post
    Cheers Mark - however, that gives me a run time error 5, Invalid procedure call or argument......

    Any ideas?
    No error when I run it so no unless you are using a Mac.

    Since you say Todays Date is in last cell with data in column E
    M.A.I.T, that isn't what the question says.
    Last edited by MARK858; Oct 9th, 2019 at 05:29 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,229
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Scroll to last cell with date

    #sharky12345 , actually are your column widths wide enough to show the full date?
    Last edited by MARK858; Oct 9th, 2019 at 05:46 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #7
    Board Regular sharky12345's Avatar
    Join Date
    Aug 2010
    Posts
    3,239
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Scroll to last cell with date

    Thanks - got it working, not sure what I'd done but it works fine now.

    Thanks to both for your help!
    There isn't a day go by without me learning something new about VBA!

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,884
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Scroll to last cell with date

    Glad we were able to help you.
    Come back here to Mr. Excel next time you need additional assistance.
    Quote Originally Posted by sharky12345 View Post
    Thanks - got it working, not sure what I'd done but it works fine now.

    Thanks to both for your help!
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •