VBA Code take too much time

[faraz502]

I am Using B/M code in VBA excel but it's too much time.

This Formula In G33:J33 Cell
=IFERROR(INDEX('INV LOG'!$B:$B,SMALL(IF($E$8='INV LOG'!$J:$J,ROW('INV LOG'!$J:$J)-ROW('INV LOG'!$J$1)+1),$X1)),"")


Range("G33:J33").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Selection.Copy
Range("G33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

 

[Saurabhj]

Hi,

Do below changes:

'Add these lines
Application.screenupdating = false
Application.Calculation = xlCalculationManual
'****

Range("G33:J33").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Selection.Copy
Range("G33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

'Add these lines
Application.ScreenUpdating = true
Application.Calculation = xlCalculationAutomatic
'****

 

[mohadin]

What about

  Application.Calculation = xlCalculationManual
    Range("G33:J33").Select
    With Range(Selection, Selection.End(xlDown))
        .FillDown
        Application.Calculation = xlCalculationAutomatic
        .Value = .Value
    End With

 

[GlennUK]

The problem is not the VBA .. it is your formula ... using array tests on full column ranges is going to be slow to fill down whether you try manually or use VBA. Do you really need to reference full columns (1,048,576 cells)?

 

[diddi]

it will be much faster if you can have a defined range and get rid of .select

 

[faraz502]

Application.Calculation = xlCalculationAutomatic

it will be much faster if you can have a defined range and get rid of .select

I GIVE A range only 300 rows but same situation....

 

[GlennUK]

I GIVE A range only 300 rows but same situation....

Show your adjusted formula.

 

[diddi]

show the whole subroutine