VBA Code take too much time

faraz502

Board Regular
Joined
Mar 26, 2014
Messages
75
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Do below changes:

VBA Code:
'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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
What about
VBA Code:
  Application.Calculation = xlCalculationManual
    Range("G33:J33").Select
    With Range(Selection, Selection.End(xlDown))
        .FillDown
        Application.Calculation = xlCalculationAutomatic
        .Value = .Value
    End With
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

Well-known Member
Joined
May 20, 2004
Messages
3,276
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,276
Office Version
  1. 2010
Platform
  1. Windows
show the whole subroutine
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,765
Members
416,202
Latest member
donya ba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top