VBA Codes to get to the right most non-blank cell

Question

I am using MS Excel 2007 and I have stuck in something which I am describing below

Let's suppose I have data in columns "A", "D". "H", "J", "L", "P" in an excel sheet, and there are hundreds of rows of data for all those said columns. And in this scenario, the cell "A4" is selected and I need to go to the cell "P4", if I have to go there by manual key-stroke, it would be like pressing 'Ctrl+Right Arrow' for 5 times. But what would be the VBA code for this process of moving from "A4" to "P4" (without making the key-strokes)? Help on this would highly be appreciated!! Thank you in advance.

@brax OP didn't want an offset of 5 columns, but to move to the right most cell :-) — T.M., Feb 07 '21 at 09:21
Suggested readings [Avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/10717999#10717999) and [Some reflections abt last used cells](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) — T.M., Feb 07 '21 at 09:29

score 1 · Answer 1 · answered Feb 07 '21 at 09:19

You don't have to simulate key strokes:

Sub SelectMostRightCell()
With Sheet1                       ' << change to project's sheet Code(Name)
    '~~> get most right column in active row
    Dim lastCol As Long
    lastCol = .Cells(ActiveCell.Row, .Columns.Count).End(xlToLeft).Column
    '~~> select most right cell in active row
    .Cells(ActiveCell.Row, lastCol).Select
End With
End Sub

btw try to avoid .Select as far as possible, i.e. if not expressly wanted

VBA Codes to get to the right most non-blank cell

1 Answers1