Speeding up a macro

[ldowling]

Good morning All,
I am not in any way good at writing a new macro but i am "okay" at manipulating several macros to do what i want, i understand the simple side of macros but after frankensteining a macro together it seems to run horribly slow with certain changes to the code ...... surprise .... surprise .....

 Set rngSearch = ws.Columns(1).Find(What:=strWhat, LookIn:=xlValues)

with this line of code i use the

LookIn:=xlValues

and it does what i want but the problem is its very very slow and when i use

LookIn:=xlFormulas

its fast but it looks in a formula which i obviously don't want ... i want to find the value created by that formula.

My question to you great people is how can i make this faster ?

i cant share the workbook but i can provide the full macro if needed.

Thank you in advance people

 

[JackDanIce]

Re: Macro Help - Speeding up a macro

I don't know how to make that specific line faster and without a better understanding of your workbook, difficult to suggest other changes.

You could try disabling calculations (Formula->Calculation->Manual) and then run my macro again but without your workbook, it's just guess work.

 

[ldowling]

Re: Macro Help - Speeding up a macro

Hello Jack,
Thank you for your help.

i have sorted the formula out, basically i am copying the column a (A28:A5000) to another column as text and using that as a reference to check.
So not really sorting, just working around the problem

This is alot faster and will be more then quick enough to use.

again, thank you

Kind Regards,
Luke Dowling

 

[rlv01]

Re: Macro Help - Speeding up a macro

using "xlValues" works but it just takes sooooo longg.

1) Do you need to use xlValues? Could you use xlFormulas instead? Here is a good discussion:

https://www.mrexcel.com/forum/excel...nce-between-find-look-values-vs-formulas.html

2) Have you tried limiting Find to only the cells in the column that are not empty? In other words:

Instead of using this

'Search Column 1
Set rngSearch = ws.Columns(1).Find(What:=strWhat, LookIn:=xlValues)

Use this

'Search Column 1
Set rngSearch = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).Find(What:=strWhat, LookIn:=xlValues)