Next non-blank cell in excel/visualBasic

Question

I'm trying to script a very basic set of keystrokes in excel where I'm setting a cell to reference a cell that is 2 to the right and then the next non-blank cell down. Very easy set of keystrokes:

= right right ctrl-down enter

But when I use the macro recorder to do this it wants to substitute the ctrl-down with a relative reference; ie

ActiveCell.FormulaR1C1 = "=R[6]C[3]"

Surely there is an easy substitution to do what I want?

Answer 1

dim Rng as range
set Rng=Activecell.offset(0,2).end(xldown)

is the equivalent of your key-strokes, but its not the next non-blank cell, its the last contiguous non-empty cell

Answer 2

Worth noting that Charles' answer will take you to the last cell with something in it before a blank cell. There could be more detail below the blank cell. When I look for the last cell in a row, I start from the bottom and work my way up.

Working from the active cell it would look like this:

Dim col As Double
Dim rw As Double
col = ActiveCell.Column + 2
rw = 65000
Debug.Print col & "-" & rw
Cells(rw, col).End(xlUp).Select

Answer 3

Worth noting that Charles' answer will take you to the last cell with something in it before a blank cell. There could be more detail below the blank cell. When I look for the last cell in a row, I start from the bottom and work my way up.

Working from the active cell it would look like this:

Dim col As Double
Dim rw As Double
col = ActiveCell.Column + 2
rw = 65000
Cells(rw, col).End(xlUp).Select