Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move to top of a column (Excel 97)

    How do I move to the top of a column using vba in Excel 97?

    Thanks,

    Chris (Hunt)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move to top of a column (Excel 97)

    Do you mean that you want to select the cell in row 1 and the same column as the active cell? If so:

    Cells(1, ActiveCell.Column).Select

  3. #3
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move to top of a column (Excel 97)

    Thanks, Hans, that is exactly what I meant. Do you also know how to move to the bottom cell of a column containing data? That would also be very useful.

    Best wishes,

    Chris

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Move to top of a column (Excel 97)

    If the active cell is in a range of cells with no gaps,

    ActiveCell.End(xlDown).Select

    will move to the bottommost non-blank cell within that range in the same column.

    If you want to select the last non-blank cell in the column overall, use

    Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select

    Note: in a macro, it is often not necessary to select cells. For example, if you want to retrieve the row number of the last non-blank cell in column A, the two lines

    Cells(Rows.Count, 1).End(xlUp).Select
    r = ActiveCell.Row

    can be replaced with the shorter and more efficient

    r = Cells(Rows.Count, 1).End(xlUp).Row

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Hatsukaichi, Hiroshima, Japan
    Posts
    904
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move to top of a column (Excel 97)

    Thanks Hans, Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select was what I needed.

    Best wishes,

    Chris

Posting Permissions

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