How to Speed up this search?

[Davy]

I have written the VB below to search a genealogical database and make
visible only those rows which have the surname of the enquirer. It works OK
but is very slow; about 10 sec per 1000 rows so the whole search takes 2
mins. Can anybody see anything I am doing wrong?

I initially used a For Each loop but that seemed to search about 64000
records.

many thanks

David

Public Sub HideRows5()

Dim FoundCell As Range
Dim Searchword
Dim Surname As String
Dim RowCount As Single
Dim RowNumber As Single

Dim MatchCount As Integer
MatchCount = 0

'Set all spreadsheet rows to visible else Find searches only visible rows
left from last search.
Worksheets("Data").Rows.Hidden = False

'Obtain Surname to search for
Surname = InputBox(Prompt:="Please type into the box below the surname to
search for. The computer will then search for all surnames and maiden names
that match. This may take a couple of minutes")

'Worksheets("Data").Activate 'Switch to the Data Worksheet for following
processing

' count number of rows having data. The column being testing must have a
value in each row.
RowCount = Cells(Rows.Count, "C").End(xlUp).Row

'Step through the rows.
For RowNumber = 2 To RowCount

' The FIND method finds specific information in a range, and returns a Range
object that represents the first cell where that information is found.
Returns Nothing if no match is found. Doesn't affect the selection or the
active cell.
Set FoundCell = Range(Cells(RowNumber, 3), Cells(RowNumber,
5)).Find(What:=Surname, _
MatchCase:=False, _
LookIn:=xlValues, LookAt:=xlPart)

If FoundCell Is Nothing Then
Rows(RowNumber).Hidden = True 'set property to hidden
Else
MatchCount = MatchCount + 1
End If

Next

MsgBox Prompt:=MatchCount & " Records Found for " & Surname & " from " &
RowCount & " records."

'Unhide the title row
Worksheets("Data").Rows(1).Hidden = False

Range("A1", "A2").Activate ' Go to top of worksheet

End Sub

 

[Trevor Shuttleworth]

Davy

you'd probably be better doing this as an AutoFilter.

Regards

Trevor

 

[Dave Peterson]

Instead of searching each row, you could just search the columns (C:E) and look
for a match.

Then keep track of where you find them. Hide everything, unhide row 1 and then
unhide the matches:

Option Explicit
Public Sub HideRows5A()

Dim FoundCell As Range
Dim Surname As String
Dim RowCount As Long
Dim FirstAddress As String
Dim FoundRange As Range

Surname = InputBox(Prompt:="Please type into the box below the " & _
"surname to" & _
" search for. The computer will then search for all " & _
"surnames and maiden names that match. " & _
vbLf & vbLf & "This may take a couple of minutes")

Surname = Trim(Surname)

If Surname = "" Then
Exit Sub
End If

With Worksheets("data")
.Rows.Hidden = False
RowCount = .Cells(.Rows.Count, "C").End(xlUp).Row
With .Range("c:e")
Set FoundCell = .Find(What:=Surname, _
MatchCase:=False, after:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlPart)

If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Set FoundRange = FoundCell
Do
If FoundRange Is Nothing Then
Set FoundRange = FoundCell
Else
Set FoundRange = Union(FoundCell, FoundRange)
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With

If FoundRange Is Nothing Then
MsgBox Surname & " wasn't found!"
.Rows.Hidden = False
Else
.Rows.Hidden = True
.Rows(1).Hidden = False
FoundRange.EntireRow.Hidden = False
MsgBox Prompt:=.Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Count _
& " Records Found for " _
& Surname & " from " & RowCount & _
" records."

End If

Application.Goto .Range("a1"), scroll:=True

End With

End Sub

=========
But I like Trevor's idea of using Data|Filter|autofilter.

If I were doing it manually, I'd insert a new column A (or column out of the
way) and put a formula that concatenates the values in columns C:E (or D:F)

=d2&"..."&e2&"..."&f2
and drag down.

Then apply data|filter|autofilter to that column.

Use Custom and contains your surname.

It worked much quicker in my simple testing.

And the fastest way may be to use Data|Filter|Advanced filter.

You set up a range with the nice headers copied from C1:E1, put the surname in a
separate row in each column (surrounded by "*" (xlpart???)) and run it.

It's pretty darn fast--you could get rid of the warning!:

Option Explicit
Sub hiderows5B()

Dim curWks As Worksheet
Dim newWks As Worksheet

Dim Surname As String
Dim RowCount As Long

Surname = InputBox(Prompt:="Please type into the box below the " & _
"surname to" & _
" search for. The computer will then search for all " & _
"surnames and maiden names that match. " & _
vbLf & vbLf & "This may take a couple of minutes")

Surname = Trim(Surname)

If Surname = "" Then
Exit Sub
End If

Application.ScreenUpdating = False

Set curWks = Worksheets("Data")
Set newWks = Worksheets.Add

With newWks
.Range("a1:c1").Value = curWks.Range("c1:e1").Value
.Range("a2").Value = "*" & Surname & "*"
.Range("b3").Value = "*" & Surname & "*"
.Range("c4").Value = "*" & Surname & "*"
End With

With curWks
If .FilterMode Then
.ShowAllData
End If
RowCount = .Cells(.Rows.Count, "C").End(xlUp).Row
Intersect(.Range("c:e"), .UsedRange).AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=newWks.Range("A1:C4"), _
Unique:=False
End With

With Application
.DisplayAlerts = False
newWks.Delete
.DisplayAlerts = True
.ScreenUpdating = True
End With

MsgBox Prompt:=curWks.UsedRange.Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Count _
& " Records Found for " _
& Surname & " from " & RowCount & _
" records."

End Sub

 

[Dave Peterson]

And I meant to add:

If you want to read more about advanced filter, visit Debra Dalgleish's site:
http://www.contextures.com/xladvfilter01.html

<<snipped>>

 

[RWN]

Is there not a problem with AutoFilter in that it has a maximum of 1,000
unique entries?
I know it was that way w/xl97 and is still a problem w/2k (found out the
hard way!).

 

[Debra Dalgleish]

An AutoFilter dropdown list will only show 1000 entries. If your column
has more than 1000 unique items, they can be filtered, but they can't
all be displayed in the dropdown list.

To AutoFilter for an item that doesn't appear in the dropdown list, you
can choose Custom from the drop-down list, and type the criteria. Also,
there are a couple of workarounds here:

http://www.contextures.com/xlautofilter02.html

 

[Davy]

Guys,

thanks for the contributions. Cos I don't use it often the VB took me about
a day to write; so I was astounded that Dave Peterson could produce a
quality VB solution straight off!

Autofilter is problematic because the database does have more than 1000
surnames and I have to search two columns (surname and mother's maiden
name). Also the application is for the general public to use in a museum
and most people could not use custom autofilter but can click on a command
button which runs VB.

Can anybody recommend a good Excel VB book for someone of my level? I have
Green & Rosenburg's 'Excel 2002 VBA' but find it just too difficult to use.
A solutions oriented guide might be better.

thanks

Davy

 

[Alan Beban]

[snip]
Autofilter is problematic because the database does have more than 1000
surnames and I have to search two columns (surname and mother's maiden
name). Also the application is for the general public to use in a museum
and most people could not use custom autofilter but can click on a command
button which runs VB.

I haven't followed this thread except for this post to which I'm
responding. You might want to see if the functions in the freely
downloadable file at http://home.pacbell.net/beban might be helpful.

ArrayRowFilter2(ArrayRowFilter1(dataRange,surname_column,surname),maidenname_column,maidenname)
will return an array of the filtered data not limited to the 1000 line
limit. It's use would involve including a number of the Array Functions
in a general code module.

Alan Beban

 

[Dave Peterson]

A lot of people like John Walkenbach's book:
Excel 2003 Power Programming with VBA

You can find a whole list at Debra Dalgleish's site:
http://www.contextures.com/xlbooks.html

Maybe you could print it and visit your local bookstore to see if you can find
one you like.

===
And if you used a helper column with a formula that concatenated the range:

=c2&"..."&d2 (or something like this).
Then apply data|filter|autofilter to that column.
Use Custom and contains your surname.