Moving to a visible cell in a Range with hidden rows

Posted by Brian P on April 25, 2001 4:11 PM

I go to a range, hide some of the rows and want to select a visible cell a few cells down from the top left cell of the range. The problem is that offset goes into the hidden range and when I tried to do this manually it goes down until it hits the hidden row and then goes back to the top the next column over... Any ideas?

Thanks

Posted by Dave Hawley on April 25, 2001 4:29 PM

Hi Brian

This code will select the fifth cell in the firts row of the visible cells.

ActiveSheet.UsedRange.SpecialCells(xlVisible).Rows(1).Cells(1, 5).Select

Dave
OzGrid Business Applications

Posted by Brian P on April 25, 2001 4:59 PM

Still need help

Dave

I filled in a range of about 10 by 10 and hid column c. I then tried the code (which seems like it should work) - but it landed in column E - the 5th column even though column C was hidden. I won't know how many columns are hidden so I need it to land on the 5th visible column which would be F.

Any additional thoughts?
Thanks very much

Posted by Dave Hawley on April 25, 2001 5:57 PM

Re: Still need help

Hi Brian

As I'm not too sure waht your criteria is for selecting the cell, this code will select the first visible cell in row 1 after the first hidden column

Sub InstallAddIn()
Dim RvisCells As Range
Dim RMyCell As Range

Set RvisCells = Rows(1).SpecialCells(xlVisible)

For Each RMyCell In RvisCells
If RMyCell.Column &LT;&GT; 1 Then
If RMyCell.Offset(0, -1).EntireColumn.Hidden = True Then
RMyCell.Select
Exit For
End If
End If
Next RMyCell

End Sub

Dave