none
xlDown RRS feed

  • Question

  • Hello Everyone!

    Is there a really simple way to goto the last cell in a column (xlDown?) that contains the same string as the current cell.

    Example:

    I have a excel list of 12k rows, I have sorted it by the Rates column (Rate 1,2,3,4,5,6).

    I now want a shortcut macro that allows me to select the top row (Rate 1) and goto the last (Rate 1) row in the column.

    Go to the Rate 2 top row and then click the shortcut keys to the macro and goto the last rate 2 row in that column.

    Obviously I could do a simple: Do while, offset style macro but this will be kinda slow.

    Any replies will be appreciated.

    Regards,

    Soliddrew

    Wednesday, March 16, 2016 9:34 AM

Answers

  • Hi,

    You can use a do while loop I think and start at the bottom of the rows and then count down?

    Please try this example:

    You will have to change the "Cells(3,2).value" and "Cells(n,2).value" to the right row and column.

    The first number is the row number of the cell you want to start with, the second the column number.

    You can also use: activecell.value instead of the "Cells(3,2).value" 

    Adjust the "n = 12000" to the last row number of your sheet.

    Dim n As Integer
    
    n = 12000
    
    Do While Cells(3, 2).Value <> Cells(n, 2).Value
    
    If n >= 1 Then
    n = n - 1
    Else
    Exit Sub
    End If
    Loop
    
    Cells(n, 2).Select

    Hope it works for you!

    Best regard,
    Yannick



    • Edited by YannickB90 Wednesday, March 16, 2016 10:08 AM
    • Marked as answer by Soliddrew Wednesday, March 16, 2016 10:21 AM
    Wednesday, March 16, 2016 10:00 AM

All replies

  • Hi,

    You can use a do while loop I think and start at the bottom of the rows and then count down?

    Please try this example:

    You will have to change the "Cells(3,2).value" and "Cells(n,2).value" to the right row and column.

    The first number is the row number of the cell you want to start with, the second the column number.

    You can also use: activecell.value instead of the "Cells(3,2).value" 

    Adjust the "n = 12000" to the last row number of your sheet.

    Dim n As Integer
    
    n = 12000
    
    Do While Cells(3, 2).Value <> Cells(n, 2).Value
    
    If n >= 1 Then
    n = n - 1
    Else
    Exit Sub
    End If
    Loop
    
    Cells(n, 2).Select

    Hope it works for you!

    Best regard,
    Yannick



    • Edited by YannickB90 Wednesday, March 16, 2016 10:08 AM
    • Marked as answer by Soliddrew Wednesday, March 16, 2016 10:21 AM
    Wednesday, March 16, 2016 10:00 AM
  • Hi Yannick,

    Thankyou for your reply. This works fantastic!

    This only takes a fraction of a second to jump to the cell I need, a lot faster than the 10+ seconds it was taking me to find manually before.

    Happy Excelling,

    Soliddrew! 


    P.S. I changed the n = 12000 to n = Range("a2").End(xlDown).Rows.Row so that i can use it on any sheet without editing that line. =)
    • Edited by Soliddrew Wednesday, March 16, 2016 10:26 AM
    Wednesday, March 16, 2016 10:23 AM
  • As long as cells are not merged...

    Cells(1, 1).End(xlDown).Select

    Thursday, July 12, 2018 6:28 PM