Finding the first cell in a column that is not hidden

[R Tanner]

Hi,

I need to find the first cell in a column that is not hidden. I am
not sure how to go about this. Any ideas?

 

[Rick Rothstein \(MVP - VB\)]

Hidden cell? Do you mean you have hidden **rows** and you are looking for
the first ROW that is not hidden? This code will do that...

Dim C As Range
For Each C In Rows
If C.Hidden Then
MsgBox "First hidden row: " & C.Address
Exit For
End If
Next

Just replace the MsgBox call with whatever you want to do with that row.

Rick

 

[R Tanner]

I am trying to figure out a way to use the find method, but I can't
figure out how to specify the criteria as being an unhidden row...

 

[Rick Rothstein \(MVP - VB\)]

To the best of my knowledge, the Find method only finds data, not column or
row 'hiddenness'.

By the way, here is a function that will return the number of the first
hidden row...

Function FirstHiddenRow()
If Not Rows(1).Hidden Then FirstHiddenRow = Split(Split(Cells. _
SpecialCells(xlCellTypeVisible).Rows.Address(False), ",")(0), ":")(1)
FirstHiddenRow = FirstHiddenRow + 1
End Function

Note this uses a totally different method to find the first hidden row than
my earlier posted code (no loops). I am not sure whether it is more
efficient than this function (built around the concept of my earlier posted
code)...

Function FirstHiddenRow()
Dim R As Range
For Each R In Rows
If R.Hidden Then
FirstHiddenRow = R.Row
Exit For
End If
Next
End Function

I'm thinking this last one may be the more efficient of the two (since the
first one makes two calls to the somewhat 'slowish' Split function).

Rick

 

[R Tanner]

Thank you for the information...I used the
'Selection.SpecialCells(xlCellTypeVisible).Select' method to retrieve
the values I was looking for...