Large file formula slow process & slow filter process

[Sathish G]

Hi ,

Am using the below vlookup formula but it taking very much time to apply and filtering the values.

Is that any other of formula to make it faster than this.



Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Output!C[-1],1,0)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Output!C[-2]:C[-1],2,0)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]=RC[1]"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Output!C,1,0)"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Output!C[2],1,0)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Output!C[4],1,0)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Volte!C[-7],1,0)"
Range("C2:I2").Select
Selection.AutoFill Destination:=Range("C2:I72001"), Type:=xlFillDefault
Range("C2:I72001").Select
Columns("A:I").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$72001").AutoFilter Field:=9, Criteria1:="#N/A"
ActiveSheet.Range("$A$1:$I$72001").AutoFilter Field:=8, Criteria1:="#N/A"
ActiveSheet.Range("$A$1:$I$72001").AutoFilter Field:=7, Criteria1:="#N/A"

 

[Sathish G]

These are the formulas am using for VLOOKUP

Taking vlookup from sheet1 to Output sheet.
Please somebody share the fastest way formulas.

=VLOOKUP(A2,Output!B:B,1,0)
=VLOOKUP(C2,Output!B:C,2,0)
=VLOOKUP(D2,Output!F:F,1,0)
=VLOOKUP(D2,Output!I:I,1,0)
=VLOOKUP(D2,Output!L:L,1,0)
=VLOOKUP(D2,Output!B:B,1,0)

Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Output!C[-1],1,0)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Output!C[-2]:C[-1],2,0)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]=RC[1]"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Output!C,1,0)"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Output!C[2],1,0)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Output!C[4],1,0)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Volte!C[-7],1,0)"
Range("C2:I2").Select
Selection.AutoFill Destination:=Range("C2:I72001"), Type:=xlFillDefault
Range("C2:I72001").Select

 

[Sathish G]

Any one pls help???

 

[mukesh098]

Try below method instead of selecting each cell

Range("C2:I2").AutoFill Destination:=Range("C2:I72001"), Type:=xlFillDefault

Also, try to paste the it as value before using filters.

 

[mukesh098]

Range("C2").FormulaR1C1 = "=VLOOKUP(RC[-2],Output!C[-1],1,0)"
Range("D2").FormulaR1C1 = "=VLOOKUP(RC[-1],Output!C[-2]:C[-1],2,0)"
Range("E2").FormulaR1C1 = "=RC[-1]=RC[1]"
Range("F2").FormulaR1C1 = "=VLOOKUP(RC[-2],Output!C,1,0)"
Range("G2").FormulaR1C1 = "=VLOOKUP(RC[-3],Output!C[2],1,0)"
Range("H2").FormulaR1C1 = "=VLOOKUP(RC[-4],Output!C[4],1,0)"
Range("I2").FormulaR1C1 = "=VLOOKUP(RC[-5],Volte!C[-7],1,0)"