Working with Hidden Rows

[Matt]

I'm trying to write code for moving the cell position down from A1 to the next row where the cell is unhidden, any ideas?

Thanks

Matt
This message was edited by Matt on 2002-03-27 07:57

 

[daleyman]

Yeah, i guess your implying that the rows are hidden simply by having rowheight=0, then you could use the following...


Sub test()
rowx = 2
Do Until Cells(rowx, 1).EntireRow.RowHeight > 0
rowx = rowx + 1
Loop
Cells(rowx, 1).Select
End Sub

 

[Colo]

Hi Matt.
If Use "Hidden Property" , we are able to know whether the row is hidden or not.

Sub test2()
Dim i As Long
With Range("A1")
Do
i = i + 1
If .Offset(i).EntireRow.Hidden = False Then Exit Do
Loop
Application.Goto .Offset(i)
End With
End Sub

Or the following method is not good (mean I do not like) but move onto the unhidden cell.

Sub test3()
With Application
.Goto Range("A1")
.SendKeys "{DOWN}", True
End With
End Sub

 

[Dave Hawley]

Hi Matt

Here is a nice easy fast way

Dim rRange As Range
Set rRange = Range("A1", Range("A65536").End(xlUp)) _
.SpecialCells(xlCellTypeVisible)

rRange.Areas(1).Cells(rRange.Areas(1).Rows.Count + 1, 1).Select

 

[Osric]

On 2002-03-29 21:46, Dave Hawley wrote:
Hi Matt

Here is a nice easy fast way

Dim rRange As Range
Set rRange = Range("A1", Range("A65536").End
(xlUp)) _
.SpecialCells(xlCellTypeVisible)

rRange.Areas(1).Cells(rRange.Areas(1).Rows.Count + 1, 1).Select

But what if there is only one cell in the first area of rRange ?

 

[Colo]

Hi Dave. That's nice and good idea!
How about this one.<PRE>
Sub test4()
On Error GoTo ErrLine
With Range("A1", Range("A65536").End(xlUp)) _
.SpecialCells(xlCellTypeVisible)
If .Areas.Count > 1 Then
.Areas(2).Item(1).Select
Else
.Areas(1).Item(1).Select
End If
End With
ErrLine:
End Sub</PRE>
This message was edited by Colo on 2002-03-29 23:57

 

[Dave Hawley]

RE: But what if there is only one cell in the first area of rRange ?

You tell me? Far as I can tell it will make no difference.

 

[Osric]

On 2002-03-29 22:21, Dave Hawley wrote:
RE: But what if there is only one cell in the first area of rRange ?

You tell me? Far as I can tell it will make no difference.

Sorry, I misread your code - it doesn't make any difference.
But ... your code selects the first cell (hidden or not) after area(1) of rRange.
The original question was to select the second visible cell.

 

[Dave Hawley]

I misread Matts question too, hee asked "I'm trying to write code for moving the cell position down from A1 to the next row where the cell is unhidden"

I read this as next HIDDEN cell. My original will select the next HIDDEN cell and not VISIBLE. This should work though

Range("A2", Range("A65536").End(xlUp)) _
.SpecialCells(xlCellTypeVisible).Range("A1").Select