none
Excel offset feature to recognize hidden rows RRS feed

  • Question

  • I want my cursor to move down from the active cell to the next visible cell in a filtered worksheet.  

    How do I perform an offset function that automatically skips over rows which are hidden? 

    For example:  If I am on a cell in row 1 and row 2 is hidden, how can I set my offset function to move "down one" to row 3?


    • Edited by Tiger29631 Monday, April 25, 2016 2:00 AM
    Monday, April 25, 2016 1:53 AM

Answers

  • I am assuming that you are looking for a VBA solution and if so, try the following code example.

    Sub Macro1()
        'This is test code to call the Function
       
        NextVisibleCell(ActiveCell).Select
       
    End Sub

    Function NextVisibleCell(rngActive As Range) As Range
        Dim r As Long
        r = 1
        Do
            If Not rngActive.Offset(r, 0).EntireRow.Hidden Then
                Set NextVisibleCell = rngActive.Offset(r, 0)
                Exit Do
            End If
            r = r + 1
            If r > 1000 Then Exit Do        'Safety to prevent eternal loop. Can delete after testing
        Loop
    End Function


    Regards, OssieMac

    Monday, April 25, 2016 4:45 AM

All replies

  • I am assuming that you are looking for a VBA solution and if so, try the following code example.

    Sub Macro1()
        'This is test code to call the Function
       
        NextVisibleCell(ActiveCell).Select
       
    End Sub

    Function NextVisibleCell(rngActive As Range) As Range
        Dim r As Long
        r = 1
        Do
            If Not rngActive.Offset(r, 0).EntireRow.Hidden Then
                Set NextVisibleCell = rngActive.Offset(r, 0)
                Exit Do
            End If
            r = r + 1
            If r > 1000 Then Exit Do        'Safety to prevent eternal loop. Can delete after testing
        Loop
    End Function


    Regards, OssieMac

    Monday, April 25, 2016 4:45 AM
  • You can use this code without loops:

    Option Explicit Const STARTCELL = "A1" 'Adjust as needed '********************************* '* Rows 3, 5, 7 and 9 are hidden '* Expected result is thus 15 (start row A1 + 10 + 4 hidden) '* Sub Test() Dim newRow As Integer newRow = GetNextNotHiddenRow(Range(STARTCELL), 10) Debug.Print "Row number: " & newRow 'Prints 15 !!!
    Range(STARTCELL).Offset(newRow).Select End Sub '**************************************************************************************** '* Find target row number excluding hidden rows '* Function GetNextNotHiddenRow(CurrentCell As Range, MoveRowsDown As Integer) As Integer Dim visibleCount As Integer Dim hiddenCount As Integer Dim targetRow As Integer Dim r As Range targetRow = 0 If MoveRowsDown > 0 Then Set r = CurrentCell.Resize(MoveRowsDown + 1, 1) 'Include all rows below CurrentCell within the range visibleCount = r.Rows.SpecialCells(xlCellTypeVisible).Count 'Get visible within the range hiddenCount = r.Rows.Count - visibleCount targetRow = CurrentCell.Row + MoveRowsDown + hiddenCount 'Pure math remains ... End If GetNextNotHiddenRow = targetRow End Function



    Best regards, George



    Monday, April 25, 2016 6:03 PM
  • Thanks for the help.  This works exactly how I need it to.

    Tuesday, May 3, 2016 2:58 AM
  • Hi Tiger29631,

    if you get the solution of your issue from the above mentioned suggestions then I would like to recommend you to mark the suggestion that solve your issue. so that other community member can also refer your mark to get solution if they have same issue like you.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 3, 2016 3:53 AM
    Moderator