Speed up code

[relo]

Hi all,

Would someoe be so kind and suggest a way I could speed up this code. The code is basically looking for specific data and coping the row onto a new sheet but it is taking several minutes to run and often comes up ith a 400 error.

Thanking you kindly in advance.

Taffy

[Richard Buttrey]

Almost impossible to say without seeing the code in the context of its workbook.

Please upload, and if there are zillions of rows remove most and just leave a reperesentative sample.

If you are using any loops to extract data rows one by one then almost certainly the answer will be to use a Data Filter to extract all thh relevant rows with one instruction.

I did note that you have a lot of Select instructions peppered throughout what code I did look at. Selecting stuff is rarely necessary. Just address the objects directly.

[relo]

Thanks for the quick reply Richard. I've attached the wb.

I've limited to 2000 rows but I'm looking at data upto 10000.

Thanks

[AB33]

You could easily use a filer code which is much faster than a loop. You could have also shortened all the 5 codes you have in a single code loop. Right now, I have not got the time to re-work the code, but if no one has replied by the end of the day, I will do it.

[Richard Buttrey]

Hi,

See the attached.

The macro is in Module 1 and is

Please Login or Register  to view this content.

I added the dynamic range name "Data" which is used by the procedure and also the criteria range at the top of the output sheet.

[relo]

UNBELIEVABLE it works a treat. It's gone down from 2/3 mins to run to 2/3 seconds. Thank you very much.
One thing the missing criteria that the macro is spitting out ("-","unknown","other", "other,other") can these be highlighted in the output as these are the fields that are outstanding.

Thanks in advance

[Richard Buttrey]

UNBELIEVABLE it works a treat. It's gone down from 2/3 mins to run to 2/3 seconds. Thank you very much.
One thing the missing criteria that the macro is spitting out ("-","unknown","other", "other,other") can these be highlighted in the output as these are the fields that are outstanding.

Thanks in advance

Data filtering is the fastest way I know of grabbing data that meets certain criteria. I'm constantly surprised at the number of people who go for the inelegant 'brute force' looping code method. Every pass through the loop means that the Macro has to jump back to the Excel App and then return to the VBE and each time it does this there is a time overhead.

I'm not sure what you mean by highlighting "-","unknown","other", "other,other". I understand the "-" because this is one of the criteria, but what are the others?Can you manually highlight the output you are referring to so that I can understand what's needed.

[relo]

Hi Richard,

My first thought was to loop, I always though data filtering was too simplistic for VBA. Such a novice. As for the output, my original code included:

If c.Value = "-" Or c.Value = "" Then
and

If c.Value = "-" Or c.Value = "unknown" Or c.Value = "other" Or c.Value = "other,other" Or c.Value = "" Then

and the code would also highlight the colour once locating the text string

Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="unknown", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0

With that in mind I would expect the output to look some like the attached. It would seem that we have only been looking at "-" instead of searching for "-" and "", "other", "other,other", "unknown".

Thanks in advance

[Richard Buttrey]

Hi,

I can only see 'other' in column V. There don't appear to be any "unknown" or "other,other"

However as a general principle if there are more OR criteria which these are, they need a new criteria. So for instance to pick up the Column V 'Property Type' 'other' records you'll need to add a new criteria. At the moment there are two criteria for property type in E6 & F7. Hence you need to introduce a new row 8 and a new column G. In G1 enter "Property Type and in G8 enter 'other'. The whole criteria range should now be A1:O16.

Don't forget to correct the output row of field names if you've used Insert to insert the new column G.

[relo]

Brilliant thank you very much. Much appreciated.