Select next non empty cell in A column – Excel VBA

July 8, 2022

Trying to have a button to select next non empty/blank cell in column A, relative to current row. The code below works, but only if active cell is in column A.
Need it to work on column A, even when active cell is in another column.

Private Sub CommandButton3_Click() 'Next step button - selects next step in A column
Dim n As Long
n = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
If ActiveCell.Row = n Then MsgBox "Last one, no more instructions.": Exit Sub

If ActiveCell.Offset(1, 0) = "" Then
    ActiveCell.End(xlDown).Select
Else
    ActiveCell.Offset(1, 0).Select
End If

End Sub

>Solution :

The problem is your activecell is in the wrong column. You need to address that:

Private Sub CommandButton3_Click() 'Next step button - selects next step in A column
Dim n As Long, fixed_range As Range
Set fixed_range = ActiveSheet.Cells(ActiveCell.Row, 1)
n = Cells(Rows.Count, fixed_range.Column).End(xlUp).Row
If fixed_range.Row = n Then MsgBox "Last one, no more instructions.": Exit Sub

If fixed_range.Offset(1, 0) = "" Then
    fixed_range.End(xlDown).Select
Else
    fixed_range.Offset(1, 0).Select
End If
End Sub

This uses a "fixed_range" to make sure we’re working on column 1.