Results 1 to 3 of 3

Thread: Using the End.(xlUp) in a macro

  1. #1

    Using the End.(xlUp) in a macro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have created a macro using the record macro function. However, I want to replace the portion where it selects the second cell by going up 4 rows and to the left 3 columns to use the End and Up key to find the top cell with a value, then go down one row before going to the left 3 columns.

    When I try to replace the portion of the code with the End.(xlUp) the macro stops working.

    Here is the code that was generated and it will work. However, the number of rows that I want to go up varies and this will always select the second cell by only going up 4 rows.


    ActiveCell.FormulaR1C1 = "=+RC[-3]-R[-4]C[-3]"

    ActiveCell.FormulaR1C1 = "=+RC[-3]-R[End.(xlUp)]C[-3]"

  2. #2
    Using Offset is simpler
    Code:
    With ActiveCell
    .Formula = "=" & .Offset(, -3).Address(1, 1) & "-" & .End(xlUp).Offset(, -3).Address(1, 1)
    End With

  3. #3
    [QUOTE=Malcolm;30987]Using Offset is simpler

    Thank You.

Posting Permissions

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