Next cell with data?

  • Transitions are from empty to nonempty or nonempty to empty. If there'd be a transition from ActiveCell to the cell immediately below it OR ActiveCell were empty, then ActiveCell.End(xlDown) would be the next nonempty cell OR to the bottommost cell in the column. If there'd be no transition AND ActiveCell were nonempty, then ActiveCell.Offset(1, 0) would be the next nonempty cell. Which suggests the following approach.

    1. Dim c As Range
    2. If Application.WorksheetFunction.CountA(ActiveCell.Resize(2, 1)) = 2 Then
    3.   ActiveCell.Offset(1, 0).Activate
    4. Else
    5.   Set c = ActiveCell.End(xlDown)
    6.   If Not IsEmpty(c.Value) Then c.Activate Else Set c = Nothing
    7.   If c Is Nothing Then
    8.     '# what do you want to do if there are no nonempty cells below ActiveCell?
    9.   End If
    10. End If