Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Yodi all,

    Say I have a loooong list where the A column content is sorted to group the entries. Each group of entries could be hundreds of rows deep. Besides find, is there a way in excel to quickly navigate to the beginning of each new group? I was thinking of a smart range name. A name comprising a formula that returns a reference to the beginning of each new category. Then using go to... I can navigate to the beginning of a a certain group. (This is theory, but any ideas from the lounge?)

    TX
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you have or create a column filled with the group name (category, ...), you can use AutoFilter to select a group and hide all others.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='766255' date='19-Mar-2009 19:42']If you have or create a column filled with the group name (category, ...), you can use AutoFilter to select a group and hide all others.[/quote]

    Good evening Hans,

    I don't think I understand your idea? I could (I suppose) filter the values in the A column I mentioned in the first post. This would filter the categories and give me access to the first item. I am not sure if the person that requested it was really wanting to filter the data. It was asked as more of a navigational request than a filter. I was just interested to know if there was an interesting way to move to each first item. Any idea of a keyboard shortcut to do this? (Just trying to think out the box)

    PS: This is not an important request. Please do not spend valuable time working out detailed solutions to this request. Its just a by the way request?!!
    Regards,
    Rudi

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Not a step through but this highlights the start of each group:
    [codebox]Sub findStart()
    Dim intRows As Long
    On Error Resume Next

    intRows = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row

    If Sheets(1).Cells(i, "A") <> Sheets(1).Cells(i - 1, "A") Then

    With Sheets(1).Cells(i, "A").Interior
    .ColorIndex = 44
    .Pattern = xlSolid
    End With
    End If

    Next i

    End Sub

    Sub ClearAll()
    Dim intRows As Long
    On Error Resume Next

    intRows = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row

    If Sheets(1).Cells(i, "A") <> Sheets(1).Cells(i - 1, "A") Then

    With Sheets(1).Cells(i, "A").Interior
    .ColorIndex = xlNone

    End With
    End If

    Next i
    End Sub
    [/codebox]
    Jerry

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi there Jezzaroo...

    Hope ya didn't spend too much time on that code. Thanks for the code. I can tweek it a little and see what can be made of it.
    Again... this question is just a sideliner...not a genuine problem I need to solve. (I just posted the question thinking that there may be a quick and interesting way of getting to the first new entry. Excel (and the lounge) has surprised me in the past with some unexpected and amazing answers. I have learned never to underestimate Excel.)

    Cheers
    Regards,
    Rudi

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Cheers
    [/quote]

    What about Hyperlinks? First create a list of group names, usually at the top of your list. Then change the names to Hyperlinks which link to the cell which begins each group.

    Tip, create a Name called AAA (so its the first in name box) that refers to the palce you have the hyperlinks. This will allow you to jump to a group and quickly go back to AAA to jump to another group.

    Hope this is of some help.

    Regards,

    Tom Duthie

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Tom,

    That is a decent suggestion. One that I completely overlooked. Tx for that tip!
    Regards,
    Rudi

Posting Permissions

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