Help with array formula

[clggmu]

Hi,

I've created a worksheet that has the following array index formula:

=IFERROR(INDEX('True Prelims'!$B:$I,SMALL(IF('True Prelims'!$I:$I=Table!$A$1,ROW('True Prelims'!$I:$I)),ROW('True Prelims'!2:2)),1),"")

Basically it works but is awfully slow when run on VBA and I was wondering if anyone could help me speed it up please? I am using the following VBA code:

Range("B3").Select
Selection.FormulaArray = _
"=IFERROR(INDEX('True Prelims'!C2:C9,SMALL(IF('True Prelims'!C9=Table!R1C1,ROW('True Prelims'!C9)),ROW('True Prelims'!R[-1])),1),"""")"
Selection.AutoFill Destination:=Range("B3:B" & lastrow), Type:=xlFillDefault
Range("B3:B" & lastrow).Select
ActiveSheet.Range("B3:B" & lastrow).RemoveDuplicates Columns:=1, Header:=xlNo

In short I want it to pull all the data in a list that follows two separate criteria, ($A$1 and $B2), however I would like it to only pull each data point once as they are recurring and it would give me a very large list.

All help is greatly appreciated as I am a bit of a VBA rookie

Thanks!

 

[XOR LX]

But of course it will run slowly! Using entire column references means that every instance of that formula is being forced to calculate over more than one million rows.

Does the data in column I of the True Prelims sheet really extend as far as row 1048576? If not, I suggest you either choose a suitably low, though sufficient, upper bound for the end row being referenced or, even better, add something to your code to detect the last-used row in that column. Do you know how to do this?

Regards

 

[clggmu]

Hi, thanks very much for your reply. I'm not sure how to add it in this example if I'm honest, do you know a piece of code I would be able to use?