PDA

View Full Version : [SOLVED] Working With Subset Of Data



Opv
02-15-2014, 12:15 PM
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
02-15-2014, 02:40 PM
easy way is to use a combobox, set the list to the column range desired and matchentry to FmMatchEntryComplete

Opv
02-15-2014, 03:54 PM
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