Working With Subset Of Data

[Opv]

I am trying to come up with a solution to tame my ever-growing address book, which is now at over 2,000 entries. I am tired of constantly scrolling up and down every time I wanted to find the row (or particular range of rows) which I am interested in viewing/editing.

I thought of an idea recently of somehow grouping rows that have entries in the "SURNAME" field that begin with the same letter so that all of the "A's, "B's," etc., could be grouped....then possibly use VBA to hide/display only one letter of the alphabet at a time. My initial thinking was to design the code using the Worksheet_BeforeDoubleClick function, i.e., start by displaying only the first row of each group, double click on, say, the row that begins with "C" and have only the "C" group displayed, close by double clicking any row within the group, etc.

What I thought was going to be a simple matter of a simple SHOW/HIDE routine has turned out not to be so simple. Needless to say I quickly exceeded my level of competence, so much so that I don't really know how to frame my question. Does the concept described above make sense?

[westconn1]

easy way is to use a combobox, set the list to the column range desired and matchentry to FmMatchEntryComplete

[Opv]

Oh well, I came up with the following block of code that does (rather crudely) what I'm wanting to do. I'm sure there is likely a better way, but it seems to work. All I have to do is tweak it a bit to accommodate my full address book.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True
    Application.ScreenUpdating = False
    Cells.EntireRow.Hidden = False
      Dim rng As Range, i As Long, MyLetter As String
      MyLetter = Left(Target.Value, 1)
      Set rng = Range("C2:C25")
      On Error Resume Next
        If Not Intersect(Target, rng) Is Nothing Then
          For i = 1 To rng.Count
            If Left(rng(i).Value, 1) = Left(MyLetter, 1) Or _
              Left(rng(i).Value, 1) <> Left(rng(i).Offset(-1).Value, 1) Then
                rng(i).EntireRow.Hidden = False
            Else: rng(i).EntireRow.Hidden = True
            End If
          Next i
        End If
    On Error Goto 0
    Application.ScreenUpdating = True
End Sub