Speed up a slow Macro?

[ttratl]

Hi Everyone,

This macro is working exactly as I want it to - except that it is incredibly slow:

Sub myfind()
Dim Message, Title, Default, SearchString
Message = "Enter the name you need to find.." & vbNewLine & "(not case sensitive)" ' Set prompt.
Title = "Find ? On all sheets!" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchStart: 'THIS IS THE NEW CODE FROM MR EXCEL
SearchString = InputBox(Message, Title, Default)

Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set F = .Find(SearchString, MatchCase:=False, LookAt:=xlPart, LookIn:=xlValues)
If F Is Nothing Then
MsgBox "I can't see " & SearchString & " in the list!"
GoTo SearchStart 'THIS IS THE NEW CODE FROM MR EXCEL
Else
Location = F.Address
S.Select
Range(Location).Select

End If
Exit For
End With
Next S

End Sub

I have changed the S.Range to B4:B50000 (as this is all that actually needs searching), thinking a smaller search range may speed it up.
Also - The worksheet it's searching was in an xls (2003) workbook, and was telling be about a compatibility issue when saving, so I copied it to a new xlsm (2007) workbook, but it is still super slow.

If I search for the last entry - using the macro - it takes 3.5 minutes!

Any ideas on wht's going wrong and how to make it fast?

Thanks

 

[MisterBates]

Where you have:

Location = F.Address
S.Select
Range(Location).Select

Try replacing with:

F.Select

And by the way, why are you using a mcro to do this when the Find dialog allows searching the whole workbook?

 

[pjmorris]

Hi ttratl

From my investigation the key determinant of the time is the Set F = .find(....... statement. In my sample sheet this single command took 14s to run on a single sheet. You don't say how many sheets you have, but this could be a factor, the other thing is the number of columns you address (upto IV), with a single column I couldn't measure the time (just using a stopwatch), but with 256 columns and 50000 rows youre asking it to search 12.8 Million cells! so perhaps 3.5 mins or 61000 cells per second isn't too bad!

I think the key thing to making this faster is to reduce the scope of the search to only that which is absolutely necessary.

Sorry to not be more helpful.

Regards

 

[ttratl]

Thanks guys,

I find the built in Find dialogue irritating. I need to search values and it takes several clicks to set it up before you even enter the search criteria. Ideally a "Search on page" field would be useful. The Find dialogue is taking over 3 minutes to search as well. I tried changing the code as suggested, but it didn't seem to make a difference.

Peter - I did try to narrow the search (to B4:B50000 as above), but that didn't speed it up either.

I think there's something wrong with this workbook as it only has 50000 rows and 10 columns. Doesn't 2007 have a million+ rows?

I'm going to try and do something different to solve my issue - but thanks both for your help.

 

[pjmorris]

Morning ttratl,

I agree, there must be something wrong with the spreadsheet. When I constrained the search to B4:B50000 it probably took less than a second to do the search, and yes Excel 2007 has 1048576 rows and 16384 columns! I think its a good idea to copy the data to a new workbook and see how you get on.

Regards