move cursor using vba code

Question

I have previously used ACTIVECELL.OFFSET(x) to move the cursor around but that method has a disadvantage. I believe it simply amends the Ry,Cy by moving the cursor to Ry+x,Cy e.g. from R1C1 to R2C1. Strictly speaking this is amending the active cell reference and not moving the cursor (as illustrated by the action of the vba code moving the cursor to a hidden cell if a filter is on; using the move cursor arrows would not do this - it would move the cursor to the next visible row).

Please tell me the correct code to use that moves the cursor down one (active) row.

Answer 1

Hello,

You need to jump to the next visible cell, not just offset the row index. Use SpecialCells(xlCellTypeVisible) to find the first visible cell below the active one, then activate it. This macro does exactly that and respects filters.

Sub MoveDownOneVisible()
    Dim startCell As Range
    Dim nextVisible As Range

    Set startCell = ActiveCell

    On Error Resume Next
    Set nextVisible = startCell.Offset(1, 0) _
        .Resize(startCell.Parent.Rows.Count - startCell.Row, 1) _
        .SpecialCells(xlCellTypeVisible)(1)
    On Error GoTo 0

    If Not nextVisible Is Nothing Then
        nextVisible.Activate
    End If
End Sub

It looks one row below the active cell, scans downward only in that column, takes the first cell that is visible, and activates it. If there is no visible row below, it leaves the selection as is. You can assign this macro to a shortcut key to mirror the Down arrow behavior on filtered lists.

Answer 2

To move the cursor down to the next visible row in Excel using VBA, you can utilize the ActiveCell.Offset method along with a loop to ensure that you only move to visible cells. Here’s a refined example of how to achieve this:

Sub MoveCursorToNextVisibleRow()
    Dim nextCell As Range
    Set nextCell = ActiveCell.Offset(1, 0) ' Start with the cell directly below the active cell

    ' Loop until a visible cell is found
    Do While nextCell.EntireRow.Hidden
        Set nextCell = nextCell.Offset(1, 0) ' Move down to the next cell
    Loop

    ' Activate the next visible cell
    nextCell.Activate
End Sub

In this code, it begins by checking the cell directly below the active cell and continues to move down until it finds a visible cell, which it then activates. This approach mimics the behavior of the arrow keys, ensuring that you do not navigate to any hidden cells.

---

References:

• Working with the Active Cell

Answer 3

My mistake. I used the term HIDDEN to refer to cells that were filtered, i now understand they are not HIDDEN but simply not visible due to the filter. Can anyone help please?