Array formula issues

[clggmu]

Hi,

I posted a similar question a few days ago and I thought I had solved the problem but something else came up. Basically what happens is my last row keeps changing as I run my array formula macro and it gets bigger and bigger every time the formula runs and as such after a few times using the macro it becomes too big and slow and stops performing properly. In short what I am looking the formula to do is to pull all the data from a different sheet that follows two separate criteria, ($A$1 and $B2), however I would like it to scan through all the rows on the "True Prelims" sheet and paste the data it finds in the "Table" sheet. However, its scanning through all the rows in the first sheet but instead of just pasting the 10 or 12 values that it finds in the "True Prelims" sheet, it is pasting those values PLUS a load of blank rows into the "Table" sheet and as such my last row keeps changing.

I hope that makes sense and I really would appreciate any help because I have been stuck with this issue for a while, I'm quite new to VBA and array formulas. My code is:


Sub Table()
'
' PullTickers Macro
'


'
Dim Lastrow As Long
Dim sht As Worksheet


Set sht = ThisWorkbook.Worksheets("True Prelims")




Lastrow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

' Clear Previous data
Range("B3:M3" & Lastrow).Select
Selection.Cells.Clear

' Pull Ticker
Range("B3").Select
Selection.FormulaArray = _
"=IFERROR(INDEX('True Prelims'!R2C2:R300C9,SMALL(IF('True Prelims'!R2C9:R300C9=Table!R1C1,ROW('True Prelims'!R2C9:R300C9)),ROW('True Prelims'!R[-1]))-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




Thanks!

 

[clggmu]

Re: HELP!: Array formula issues

Apologies, start of that code should be:

Dim lastrow As Long
lastrow = ActiveSheet.UsedRange.Rows.Count