Long & Messy Code, hoping I can speed this up?

Question

I have a very long & messy code (please don't hate this took me a long time to learn haha) and I am wondering if anyone knows how to speed this up / cut down some of the code, it currently runs the same code against 50 rows even though usually I only require it to run against 10 or 20 rows, is there a way to have the code check how many rows are populated and then only run the code against those rows?

Range("J2").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A1))),"""")"
Range("J3").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A2))),"""")"
Range("J4").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A3))),"""")"
Range("J5").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A4))),"""")"
Range("J6").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A5))),"""")"
Range("J7").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A6))),"""")"
Range("J8").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A7))),"""")"
Range("J9").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A8))),"""")"
Range("J10").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A9))),"""")"
Range("J11").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A10))),"""")"
Range("J12").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A11))),"""")"
Range("J13").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A12))),"""")"
Range("J14").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A13))),"""")"
Range("J15").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A14))),"""")"
Range("J16").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A15))),"""")"
Range("J17").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A16))),"""")"
Range("J18").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A17))),"""")"
Range("J19").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A18))),"""")"
Range("J20").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A19))),"""")"
Range("J21").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A20))),"""")"
Range("J22").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A21))),"""")"
Range("J23").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A22))),"""")"
Range("J24").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A23))),"""")"
Range("J25").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A24))),"""")"
Range("J26").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A25))),"""")"
Range("J27").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A26))),"""")"
Range("J28").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A27))),"""")"
Range("J29").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A28))),"""")"
Range("J30").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A29))),"""")"
Range("J31").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A30))),"""")"
Range("J32").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A31))),"""")"
Range("J33").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A32))),"""")"
Range("J34").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A33))),"""")"
Range("J35").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A34))),"""")"
Range("J36").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A35))),"""")"
Range("J37").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A36))),"""")"
Range("J38").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A37))),"""")"
Range("J39").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A38))),"""")"
Range("J40").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A39))),"""")"
Range("J41").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A40))),"""")"
Range("J42").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A41))),"""")"
Range("J43").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A42))),"""")"
Range("J44").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A43))),"""")"
Range("J45").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A44))),"""")"
Range("J46").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A45))),"""")"
Range("J47").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A46))),"""")"
Range("J48").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A47))),"""")"
Range("J49").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A48))),"""")"
Range("J50").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A49))),"""")"
Range("J51").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A50))),"""")"

Range("K1").Value = "Order Qty"
Range("K1").Font.Bold = True
Range("K2").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A1))),"""")"
Range("K3").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A2))),"""")"
Range("K4").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A3))),"""")"
Range("K5").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A4))),"""")"
Range("K6").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A5))),"""")"
Range("K7").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A6))),"""")"
Range("K8").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A7))),"""")"
Range("K9").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A8))),"""")"
Range("K10").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A9))),"""")"
Range("K11").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A10))),"""")"
Range("K12").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A11))),"""")"
Range("K13").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A12))),"""")"
Range("K14").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A13))),"""")"
Range("K15").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A14))),"""")"
Range("K16").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A15))),"""")"
Range("K17").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A16))),"""")"
Range("K18").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A17))),"""")"
Range("K19").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A18))),"""")"
Range("K20").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A19))),"""")"
Range("K21").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A20))),"""")"
Range("K22").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A21))),"""")"
Range("K23").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A22))),"""")"
Range("K24").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A23))),"""")"
Range("K25").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A24))),"""")"
Range("K26").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A25))),"""")"
Range("K27").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A26))),"""")"
Range("K28").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A27))),"""")"
Range("K29").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A28))),"""")"
Range("K30").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A29))),"""")"
Range("K31").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A30))),"""")"
Range("K32").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A31))),"""")"
Range("K33").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A32))),"""")"
Range("K34").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A33))),"""")"
Range("K35").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A34))),"""")"
Range("K36").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A35))),"""")"
Range("K37").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A36))),"""")"
Range("K38").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A37))),"""")"
Range("K39").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A38))),"""")"
Range("K40").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A39))),"""")"
Range("K41").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A40))),"""")"
Range("K42").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A41))),"""")"
Range("K43").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A42))),"""")"
Range("K44").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A43))),"""")"
Range("K45").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A44))),"""")"
Range("K46").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A45))),"""")"
Range("K47").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A46))),"""")"
Range("K48").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A47))),"""")"
Range("K49").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A48))),"""")"
Range("K50").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A49))),"""")"
Range("K51").Select
Selection.FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A50))),"""")"

Any help much appreciated!

Start by [finding the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Sep 28 '20 at 14:02
Then [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Sep 28 '20 at 14:02

score 1 · Accepted Answer · answered Sep 28 '20 at 14:07

1

This should do the trick.

Range("K1").Value = "Order Qty"
Range("K1").Font.Bold = True

Dim i As Integer
Dim lastRow as integer
lastRow = Range("J2").End(xlDown).Row

For i = 1 To lastRow
    Range("J" & i + 1).FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$A:$A, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$A:$A))+1, """"), ROW(A" & i & "))),"""")"
    Range("K" & i + 1).FormulaArray = "=IFERROR(INDEX('Z:\Customer Operations\[O.xlsx]S'!$D:$D, SMALL(IF(A2='Z:\Customer Operations\[O.xlsx]S'!$K:$K, ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D)-MIN(ROW('Z:\Customer Operations\[O.xlsx]S'!$D:$D))+1, """"), ROW(A" & i & "))),"""")"
Next i

answered Sep 28 '20 at 14:07

horst

781
1
6
14

Thanks for the reply, with this formula I get error "Overflow" and the debugger highlights this part: "lastRow = Range("J2").End(xlDown).Row" – PaulDN Sep 28 '20 at 15:02
Sorry, change `Dim i as Double` – horst Sep 28 '20 at 16:04
He means `Dim lastRow as Long` and `Dim i as Long` – braX Sep 28 '20 at 17:45
Thank you very much for your help & time! this code does work however once it has populated all of the rows in J & K the code just "freezes" and doesn't execute the rest of the macro, any ideas? – PaulDN Sep 29 '20 at 06:50

Long & Messy Code, hoping I can speed this up?

1 Answers1