ActiveCell.Rows(row number.Hidden returns false when row is hidden sometimes

**Brandon2013** · 03-08-2014, 01:41 PM

I am trying to implement a very simple user form that has previous and next buttons to navigate through sheet that has rows filtered out.

I was hoping to use the Hidden property to let me know whether to select an unfiltered (visible) row or not. However, sometimes it is returning False on a row that is not visible. I can not figure out why. it also seems dependent on where the active selection is. For example, if Rows 35 and 40 are visible, and selection is on row 40, then it returns False if I test for row 36.

If selection is on row 35, it does correctly return true for row 36.

Here's some basic test code I wrote to try and figure out how Hidden works:

Sub isHidden()

Dim isHidden As Boolean

isHidden = ActiveCell.Rows(36).Hidden

If isHidden Then
  MsgBox "36 is hidden"
Else
  MsgBox "36 is not hidden"
End If

isHidden = ActiveCell.Rows(37).Hidden

If isHidden Then
  MsgBox "37 is hidden"
Else
  MsgBox "37 is not hidden"
End If

End Sub

I can attach a simple file if you think it's dependent on my particular data.

Thanks!

**JBeaucaire** · 03-09-2014, 03:56 PM

Here's a technique I just confirmed is working:

Option Explicit

Sub GoNEXT()
Dim i As Long
i = 1

Do
    If Not Intersect(ActiveCell.EntireColumn.SpecialCells(xlCellTypeVisible), ActiveCell.Offset(i)) Is Nothing Then
        ActiveCell.Offset(i).Select
        Exit Do
    End If
    i = i + 1
Loop

End Sub


Sub GoPREV()
Dim i As Long
i = 1

Do
    If Not Intersect(ActiveCell.EntireColumn.SpecialCells(xlCellTypeVisible), ActiveCell.Offset(-i)) Is Nothing Then
        ActiveCell.Offset(-i).Select
        Exit Do
    End If
    i = i + 1
Loop

End Sub

**mikerickson** · 03-09-2014, 04:08 PM

ActiveCell.Rows(36) is the cell 35 rows below the active cell. If the ActiveCell is B10, ActiveCell.Rows(36) is B45
ActiveSheet.Rows(36) is row 36 of the active sheet.