Working with Hidden Rows
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Working with Hidden Rows

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    England
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?

  6. #6
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dave. That's nice and good idea!
    How about this one.


    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


    [ This Message was edited by: Colo on 2002-03-29 23:57 ]

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #9
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com