VBA Help

THRASHER69 · Mar 17, 2020

Hello,

I'm hoping some one can help me out. The part of my code listed below really seems to slow my macro down. Anyone know a better way to write this to make it run faster?

VBA Code:

    Set iRow = Range("I2", Range("I2").End(xlDown))

    With iRow
        .FormatConditions.Add Type:=xlExpression, Formula1:= _ 
           "=IF($I2<$G2,TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = vbYellow
                .TintAndShade = 0
            End With
        End With
    End With

    Set iRow1 = Range("J2", Range("J2").End(xlDown))

    With iRow1
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($J2<$G2,TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = vbYellow
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow2 = Range("C2", Range("C2").End(xlDown))

    With iRow2
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF(N2=""Released to Warehouse"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 22
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow3 = Range("E2", Range("E2").End(xlDown))

    With iRow3
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF(N2=""Released to Warehouse"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 22
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow4 = Range("N2", Range("N2").End(xlDown))

    With iRow4
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF(N2=""Released to Warehouse"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 22
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow5 = Range("C1")

    With iRow5
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(N:N,""Released to Warehouse"")"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 22
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow6 = Range("E1") 

    With iRow6
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(N:N,""Released to Warehouse"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 22
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow7 = Range("D2", Range("D2").End(xlDown)) 

    With iRow7
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($N2=""Staged/Pick Confirmed"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 4
                .TintAndShade = 0
            End With
        End With
    End With
    
     Set iRow8 = Range("N2", Range("N2").End(xlDown))

    With iRow8
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($N2=""Staged/Pick Confirmed"",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 4
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow9 = Range("C2", Range("C2").End(xlDown))

    With iRow9
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($O2=TODAY(),TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 43
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow10 = Range("O2", Range("O2").End(xlDown)) 

    With iRow10
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($O2=TODAY(),TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 43
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow11 = Range("A2", Range("A2").End(xlDown)) 

    With iRow11
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($P2<>"""",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 35
                .TintAndShade = 0
            End With
        End With
    End With
    
    LastRow3 = Cells(Rows.Count, "F").End(xlUp).Row 
    
    Set iRow12 = Range("P2:P" & LastRow3)

    With iRow12
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($P2<>"""",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 35
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow13 = Range("B1")

    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""1320 EAST LOS ANGELES AVENUE,SHAFTER,CA,93263,US"")"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""10353 RICHMOND AVENUE,HOUSTON,TX,77042,US"")"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""19417 COLOMBO STREET,BAKERSFIELD,CA,93308,US"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""2040 OREGON STREET,ODESSA,TX,79764,US"")"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""1444 NORTH DERRICK DRIVE,CASPER,WY,82604,US"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""10906 FM 2920 ROAD,TOMBALL,TX,77375,US"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""21255 LA HIGHWAY 1 SOUTH,PLAQUEMINE,LA,70764,US"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""9870 EAST 30TH STREET,INDIANAPOLIS,IN,46229,US"")"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow13
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=COUNTIF(B:B,""8708 WEST LITTLE YORK,SUITE 100,HOUSTON,TX,77040,US"")" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    Set iRow14 = Range("B2", Range("B2").End(xlDown))

    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""1320 EAST LOS ANGELES AVENUE,SHAFTER,CA,93263,US"",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""10353 RICHMOND AVENUE,HOUSTON,TX,77042,US"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""19417 COLOMBO STREET,BAKERSFIELD,CA,93308,US"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""2040 OREGON STREET,ODESSA,TX,79764,US"",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""1444 NORTH DERRICK DRIVE,CASPER,WY,82604,US"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""10906 FM 2920 ROAD,TOMBALL,TX,77375,US"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""21255 LA HIGHWAY 1 SOUTH,PLAQUEMINE,LA,70764,US"",TRUE,FALSE)" 
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""9870 EAST 30TH STREET,INDIANAPOLIS,IN,46229,US"",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With
    
    With iRow14
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($B2=""8708 WEST LITTLE YORK SUITE 100,HOUSTON,TX,77040,US"",TRUE,FALSE)"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .ColorIndex = 8
                .TintAndShade = 0
            End With
        End With
    End With

Thank you

THRASHER69 · Mar 26, 2020

I understand. Let me see if I can change the information and give you a small sample of the data

THRASHER69 · Mar 26, 2020

Does the following give you data to work with? It's part of the original data sheet. The original sheet is 31,317 rows but that will get knocked down to around 900 rows after I run my code.

FNDWRR.exe

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

BF

BG

BH

BI

BJ

BK

1

Organization Name

Order Manager

Order Number

Line

Customer

Ship To Address

Customer PO Number

Revenue Business Unit

Final Destination

Item Number

Inventory Item Status Code

Description

Product Line Reporting Cat

Qty

Reserved Qty

Onhand Quantity

Available Quantity

PO and Req Open Quantity

WIP Jobs Quantity

Manufacturing Group

Planner Code

Buyer Code

Sales Order Date

Date Booked

Customer Requested Date

MFG Completion Date

Estimated Ship Date

Post MFG Lead Time

Delivery Lead Time

Promise Date

Ship Set

On Hold

Hold Type

Picked Date

Actual Ship Date

Order Line Status

Delivery ID

Shipping Status

UOM

Shipped Qty

Invoiced Qty for Entire Line

Unit Selling Price

Unit Item Standard Cost

Rig Name

Rig Name Description

Rig Type

Order Type

Sales Order Currency

Currency Conversion Rate Type

Currency Conversion Rate

Project

Project Name

Project Task Number

Project Task Name

Subject

Incoterms

Incoterms Location

Scheduled Revenue Days

Payment Schedule

Line Type

Ordered Quantity UOM

Qty id

Reservation type

2

OUR MFG Inventory

341233

2.1

Customer 1

Address 1

17525135-001

Active

Part 1

122003.POL ROD ACCESS A2120.

11

292

270

0

ASSEMBLY

Buyer

25-MAR-2020

16-APR-2020

0

16-APR-2020

Awaiting Shipping

Ready to Release

EA

57.13

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44581118

3

OUR MFG Inventory

341233

1.1

Customer 1

Address 1

17524935-001

Active

Part 2

122003.POL ROD ACCESS A2105.

4

73

35

ASSEMBLY

25-MAR-2020

16-APR-2020

0

16-APR-2020

Awaiting Shipping

Ready to Release

EA

159.82

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44581120

4

OUR MFG Inventory

341239

1.1

Customer 2

Address 2

17524926-001

Active

Part 3

122006.PUMPING ACCESS A1290.

20

165

ASSEMBLY

25-MAR-2020

16-APR-2020

15-APR-2020

0

15-APR-2020

Production Open

Ready to Release

ST

23.70

INTERNAL ORDER TYPE

USD

EXW

Standard Line

ST

44581134

Job or Schedule

5

OUR MFG Inventory

341239

4.1

Customer 2

Address 2

17523338-001

Active

Part 4

122001.PATCO STUFFING A2990.

4

97

VIC

Buyer

25-MAR-2020

16-APR-2020

15-APR-2020

0

15-APR-2020

Awaiting Shipping

Ready to Release

ST

59.29

INTERNAL ORDER TYPE

USD

EXW

Standard Line

ST

44581127

6

OUR MFG Inventory

341239

2.1

Customer 2

Address 2

17531918-001

Active

Part 5

122006.PUMPING ACCESS A1201.

4

287

CARRIE

Buyer

25-MAR-2020

16-APR-2020

15-APR-2020

0

15-APR-2020

Awaiting Shipping

Ready to Release

EA

41.58

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44581128

7

OUR MFG Inventory

341239

3.1

Customer 2

Address 2

17525174-001

Active

Part 6

122001.STUFFING BOXES A1160.

1

123

83

ASSEMBLY

Buyer

25-MAR-2020

16-APR-2020

15-APR-2020

0

15-APR-2020

Awaiting Shipping

Ready to Release

ST

9.01

INTERNAL ORDER TYPE

USD

EXW

Standard Line

ST

44581129

8

OUR MFG Inventory

341098

1.1

Customer 3

Address 3

17525155-001

Active

Part 7

122003.POL ROD ACCESS A2120.

6

48

66

MFG

Buyer

20-MAR-2020

21-MAR-2020

25-MAR-2020

Booked

84.02

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

52559019

9

OUR MFG Inventory

341098

2.1

Customer 3

Address 3

17531597-001

Active

Part 8

122003.POL ROD ACCESS A219X.

30

1064

700

CONSIGNED

Buyer

20-MAR-2020

21-MAR-2020

25-MAR-2020

Booked

5.94

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

52559020

10

OUR MFG Inventory

341098

3.1

Customer 3

Address 3

17531876-001

Active

Part 9

122001.STUFFING BOXES A1170.

50

0

50

ASSEMBLY

20-MAR-2020

21-MAR-2020

25-MAR-2020

Booked

50.88

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

52559021

11

OUR MFG Inventory

341098

4.1

Customer 3

Address 3

17524817-001

Active

Part 10

122003.POL ROD ACCESS A2190.

30

10

20

ASSEMBLY

20-MAR-2020

21-MAR-2020

25-MAR-2020

Booked

2.62

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

52559022

12

OUR MFG Inventory

341104

2.1

Customer 3

Address 3

17524971-001

Active

Part 11

122003.POL ROD ACCESS A21S5.

1

22

20

ASSEMBLY

22-MAR-2020

14-APR-2020

08-APR-2020

0

08-APR-2020

Awaiting Shipping

Ready to Release

EA

159.42

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44578133

13

OUR MFG Inventory

341104

3.1

Customer 3

Address 3

17524976-001

Active

Part 12

122003.POL ROD ACCESS A2120.

8

234

41

ASSEMBLY

22-MAR-2020

14-APR-2020

08-APR-2020

0

08-APR-2020

Awaiting Shipping

Ready to Release

EA

38.79

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44578134

14

OUR MFG Inventory

341104

4.1

Customer 3

Address 3

17922557-001

Active

Part 13

122002.PUMPING ACCESS A1204.

2

0

17

ASSEMBLY

22-MAR-2020

11-DEC-2020

09-DEC-2020

0

09-DEC-2020

Production Open

Ready to Release

EA

245.25

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44578151

Job or Schedule

15

OUR MFG Inventory

341104

1.1

Customer 3

Address 3

17524963-001

Active

Part 14

122001.SURE PAK PACK'G A1163.

8

0

8

ASSEMBLY

22-MAR-2020

14-APR-2020

08-APR-2020

0

08-APR-2020

Awaiting Shipping

Ready to Release

ST

48.05

INTERNAL ORDER TYPE

USD

EXW

Standard Line

ST

44578135

16

OUR MFG Inventory

341104

6.1

Customer 3

Address 3

17532915-001

Active

Part 15

122003.POL ROD ACCESS A210X.

40

28

10

43

ASSEMBLY

22-MAR-2020

14-APR-2020

08-APR-2020

0

08-APR-2020

Awaiting Shipping

Ready to Release

EA

3.39

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44578136

17

OUR MFG Inventory

341104

5.1

Customer 3

Address 3

17531918-001

Active

Part 16

122006.PUMPING ACCESS A1201.

3

287

CARRIE

Buyer

22-MAR-2020

14-APR-2020

08-APR-2020

0

08-APR-2020

Awaiting Shipping

Ready to Release

EA

41.58

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44578137

18

OUR MFG Inventory

341102

2.1

Customer 2

Address 2

17531107-001

Active

Part 17

122001.BS DPSB NIT DOME A11P6.

2

ASSEMBLY

Buyer

22-MAR-2020

27-NOV-2020

24-NOV-2020

0

24-NOV-2020

Production Open

Ready to Release

EA

161.66

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44578150

Job or Schedule

19

OUR MFG Inventory

341102

1.1

Customer 2

Address 2

17525135-001

Active

Part 18

122003.POL ROD ACCESS A2120.

40

292

270

0

ASSEMBLY

Buyer

22-MAR-2020

14-APR-2020

0

14-APR-2020

Awaiting Shipping

Ready to Release

EA

57.13

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44578123

20

OUR MFG Inventory

341103

8.1

Customer 5

Address 5

17522464-001

Active

Part 19

122006.COMMON PARTS CO A99XX.

1

183

VIC

Buyer

22-MAR-2020

14-APR-2020

13-APR-2020

0

13-APR-2020

Awaiting Shipping

Ready to Release

EA

0.75

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44578124

21

OUR MFG Inventory

341103

5.1

Customer 5

Address 5

17532551-001

Active

Part 20

122001.STUFFING BOXES A1153.

3

55

24

50

ASSEMBLY

Buyer

22-MAR-2020

14-APR-2020

13-APR-2020

0

13-APR-2020

Awaiting Shipping

Ready to Release

EA

404.22

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44578125

22

OUR MFG Inventory

341103

1.1

Customer 5

Address 5

17524938-001

Active

Part 21

122003.POL ROD ACCESS A2105.

3

21

4

0

ASSEMBLY

Buyer

22-MAR-2020

14-APR-2020

13-APR-2020

0

13-APR-2020

Awaiting Shipping

Ready to Release

EA

248.68

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44578126

23

OUR MFG Inventory

341103

2.1

Customer 5

Address 5

17524977-001

Active

Part 22

122003.POL ROD ACCESS A2120.

2

14

ASSEMBLY

Buyer

22-MAR-2020

14-APR-2020

13-APR-2020

0

13-APR-2020

Awaiting Shipping

Ready to Release

EA

57.19

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44578127

24

OUR MFG Inventory

341104

7.1

Customer 3

Address 3

17523338-001

Active

Part 23

122001.PATCO STUFFING A2990.

5

97

VIC

Buyer

22-MAR-2020

14-APR-2020

08-APR-2020

0

08-APR-2020

Awaiting Shipping

Ready to Release

ST

59.29

INTERNAL ORDER TYPE

USD

EXW

Standard Line

ST

44578138

25

OUR MFG Inventory

341173

9.1

Customer 3

Address 3

17523338-001

Active

Part 24

122001.PATCO STUFFING A2990.

10

97

VIC

Buyer

24-MAR-2020

15-APR-2020

0

15-APR-2020

Awaiting Shipping

Ready to Release

ST

59.29

INTERNAL ORDER TYPE

USD

EXW

Standard Line

ST

44580118

26

OUR MFG Inventory

341173

5.1

Customer 3

Address 3

17524986-001

Active

Part 25

122006.PUMPING ACCESS A1290.

20

0

ASSEMBLY

24-MAR-2020

15-APR-2020

0

15-APR-2020

Awaiting Shipping

Ready to Release

ST

28.63

INTERNAL ORDER TYPE

USD

EXW

Standard Line

ST

44580120

27

OUR MFG Inventory

341173

2.1

Customer 3

Address 3

17524928-001

Active

Part 26

122003.POL ROD ACCESS A2102.

10

9

ASSEMBLY

24-MAR-2020

15-APR-2020

0

15-APR-2020

Awaiting Shipping

Ready to Release

EA

112.70

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44580122

28

OUR MFG Inventory

341173

4.1

Customer 3

Address 3

17524977-001

Active

Part 27

122003.POL ROD ACCESS A2120.

2

14

ASSEMBLY

24-MAR-2020

15-APR-2020

0

15-APR-2020

Awaiting Shipping

Ready to Release

EA

57.19

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44580123

29

OUR MFG Inventory

341173

3.1

Customer 3

Address 3

17524934-001

Active

Part 28

122003.POL ROD ACCESS A2105.

6

0

ASSEMBLY

24-MAR-2020

15-APR-2020

0

15-APR-2020

Awaiting Shipping

Ready to Release

EA

286.28

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44580124

30

OUR MFG Inventory

341173

1.1

Customer 3

Address 3

17524924-001

Active

Part 29

122003.POL ROD ACCESS A2120.

14

0

ASSEMBLY

24-MAR-2020

15-APR-2020

0

15-APR-2020

Awaiting Shipping

Ready to Release

EA

80.17

INTERNAL ORDER TYPE

USD

EXW

Standard Line

EA

44580125

NOV 3164 Order-Ship Details 0

Fluff · Mar 26, 2020

If you start of with 31,000 rows, the loops will definitely be slowing things down
At the moment you are deleting rows based on col AJ & from the sample of your data col Al has ready to be released for those rows that need to be kept.
Is this always the case?

rlv01 · Mar 26, 2020

THRASHER69 said:
The message box said 0.0546875

You should extend Fluff's example by methodically adding timer statements throughout your code to identify via measurement where it is slowing down. Example:

VBA Code:

Private Sub Import()

    Dim rng As Range
    Dim rg As Range
    Dim cond1 As FormatCondition
    Dim workrange As Range
    Dim Firstrow As Integer
    Dim LastRow As Integer
    Dim Lrow As Integer
    Dim LastRow1 As Long
    Dim LastRow2 As Long
    Dim LastRow3 As Long
    Dim i As Long
    Dim iRow As Range, cell As Range
    Dim iRow1 As Range
    Dim iRow2 As Range
    Dim iRow3 As Range
    Dim iRow4 As Range
    Dim iRow5 As Range
    Dim iRow6 As Range
    Dim iRow7 As Range
    Dim iRow8 As Range
    Dim iRow9 As Range
    Dim iRow10 As Range
    Dim iRow11 As Range
    Dim iRow12 As Range
    Dim iRow13 As Range
    Dim iRow14 As Range
    Dim r As Range


    Dim ST As Single, S As String                     'for code execution timer
    ST = Timer                                        'for code execution timer

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.PrintCommunication = False

    If WorksheetExists("Orders") Then
    Else


        Sheets("DO Ship Days").Visible = True
        Sheets.Add.Name = "Orders"
        Sheets("Orders").Move after:=Sheets("Sheet1")

        Sheets("Orders").Activate

        For Each w In Workbooks
            If w.Name Like "*FNDWRR*" Or w.Name Like "*3164*" Then
                Windows(w.Name).Activate
                Cells.Select
                Selection.Copy
                Windows("Oracle Orders.xlsm").Activate
                Cells.Select
                ActiveSheet.Paste
                Exit For
            End If
        Next w

        S = S & "Elaped time for Section 1 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer                                    'for code execution timer

        'Find first and last used row
        Range("AJ:AJ").Select                         ' Order Line Status
        Firstrow = ActiveSheet.UsedRange.Cells(1).Row
        LastRow = Cells(Rows.count, "AJ").End(xlUp).Row

        'Loop through used cells backwards and delete if needed
        For Lrow = LastRow To Firstrow Step -1
            Set workrange = Cells(Lrow, "AJ")
            If workrange.Value <> "Awaiting Fulfillment " _
               And workrange.Value <> "Awaiting Shipping" _
               And workrange.Value <> "Order Line Status" _
               And workrange.Value <> "Picked Partial  " _
               And workrange.Value <> "Closed " _
               And workrange.Value <> "Picked " _
               And workrange.Value <> "Production Open" _
               And workrange.Value <> "Production Partial" _
               And workrange.Value <> "Shipped" _
               And workrange.Value <> "Supply Eligible" _
               And workrange.Value <> "Ready To Release" _
               Then workrange.EntireRow.Delete
        Next Lrow


        S = S & "Elaped time for Section 2 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer


        LastRow3 = Cells(Rows.count, "E").End(xlUp).Row

        For Each r In Range("AL2:AL" & LastRow3)
            If r = "" Then r = r.Offset(, -2).Value
        Next

        ActiveSheet.Cells.Font.Name = "Kurale"
        ActiveSheet.Cells.Font.Size = 12


        Range("A:A,G:H,K:K,M:M,T:T,V:V,AB:AC,AM:AM,AO:AO,AR:AT,AV:BL").Delete

        Range("B1").Value = "Order#"
        Range("J1").Value = "Rsvd"
        Range("K1").Value = "OH"
        Range("L1").Value = "Avbl"
        Range("M1").Value = "PO/Req Qty"
        Range("N1").Value = "WIP"
        Range("S1").Value = "Ship Date"

        Sheets("Orders").AutoFilterMode = False
        Range("A1:AG1").AutoFilter
        Columns("A:AG").EntireColumn.AutoFit

        With Range("A:AG")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With

        S = S & "Elaped time for Section 3 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer


        Columns("D:E").Cut
        Columns("A").Insert Shift:=xlToRight

        Columns("C").Cut                              ' Order Manager
        Columns("AH").Insert Shift:=xlToRight

        Columns("F:M").Cut
        Columns("D").Insert Shift:=xlToRight

        Columns("Z").Cut                              ' Order Line Status
        Columns("L").Insert Shift:=xlToRight

        Columns("AB").Cut                             ' Shipping Status
        Columns("M").Insert Shift:=xlToRight

        Columns("T").Cut                              ' Ship Date
        Columns("N").Insert Shift:=xlToRight

        Columns("W").Cut                              ' Ship Set
        Columns("O").Insert Shift:=xlToRight

        Columns("AD").Cut
        Columns("P").Insert Shift:=xlToRight

        Columns("AC").Cut
        Columns("Q").Insert Shift:=xlToRight

        Columns("AE").Cut                             ' Unit Standard Cost
        Columns("Q").Insert Shift:=xlToRight

        Range("R1").Value = "Delivery#"
        Range("D1").Value = "Item#"

        Columns("Q").Insert Shift:=xlToRight

        Range("Q1").Value = "Line Total"


        S = S & "Elaped time for Section 4 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        LastRow2 = Cells(Rows.count, "A").End(xlUp).Row
        Range("Q2:Q" & LastRow2).Formula = "=SUM(P2*F2)"

        Range("A:B,E:E,L:M,U:AH").NumberFormat = "@"
        Range("C:D,F:K,O:O,S:T").NumberFormat = "0.00"
        Range("P:R").NumberFormat = "$#,##0.00"

        Columns("A:AH").EntireColumn.AutoFit

        Columns("A:B").ColumnWidth = 20
        Columns("E:E").ColumnWidth = 24
        Columns("L:L").ColumnWidth = 10

        Columns("A:B").HorizontalAlignment = xlLeft
        Columns("E:E").HorizontalAlignment = xlLeft

        Application.CutCopyMode = False

        ActiveWindow.FreezePanes = False
        Range("D2").Select
        ActiveWindow.FreezePanes = True

        Sheets("Orders").Activate

        Lrow = Range("A" & Rows.count).End(xlUp).Row
        Set rng = Range("A1:AH" & Lrow)

        With rng.Borders
            .LineStyle = xlContinuous
            .Color = vbBlack
            .Weight = xlThin
        End With

        Columns("Q").Cut                              ' Line Total
        Columns("D").Insert Shift:=xlToRight

        Sheets("Orders").AutoFilterMode = False
        Range("A1:AH1").AutoFilter

        Range("G:J").NumberFormat = "0"


        S = S & "Elaped time for Section 5 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Columns("G:G").TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True

        Columns("H:H").TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True

        Columns("I:I").TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True

        Columns("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True


        S = S & "Elaped time for Section 6 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Set iRow = Range("I2", Range("I2").End(xlDown))

        With iRow
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($I2<$G2,TRUE,FALSE)"    ' IF($OH < Qty ,TRUE,FALSE)
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = vbYellow
                    .TintAndShade = 0
                End With
            End With
        End With

        S = S & "Elaped time for Section 7 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Set iRow1 = Range("J2", Range("J2").End(xlDown))    'Avbl

        With iRow1
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($J2<$G2,TRUE,FALSE)"    ' IF(Avbl < Qty ,TRUE,FALSE)
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = vbYellow
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow2 = Range("C2", Range("C2").End(xlDown))

        With iRow2
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF(N2=""Released to Warehouse"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow3 = Range("E2", Range("E2").End(xlDown))    ' Item#

        With iRow3
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF(N2=""Released to Warehouse"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow4 = Range("N2", Range("N2").End(xlDown))

        With iRow4
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF(N2=""Released to Warehouse"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow5 = Range("C1")

        With iRow5
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(N:N,""Released to Warehouse"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow6 = Range("E1")

        With iRow6
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(N:N,""Released to Warehouse"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow7 = Range("D2", Range("D2").End(xlDown))

        With iRow7
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($N2=""Staged/Pick Confirmed"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 4
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow8 = Range("N2", Range("N2").End(xlDown))

        With iRow8
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($N2=""Staged/Pick Confirmed"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 4
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow9 = Range("C2", Range("C2").End(xlDown))

        With iRow9
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($O2=TODAY(),TRUE,FALSE)"    ' Ship Date
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 43
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow10 = Range("O2", Range("O2").End(xlDown))

        With iRow10
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($O2=TODAY(),TRUE,FALSE)"    ' Ship Date
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 43
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow11 = Range("A2", Range("A2").End(xlDown))

        With iRow11
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($P2<>"""",TRUE,FALSE)"    ' Ship Set
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 35
                    .TintAndShade = 0
                End With
            End With
        End With

        LastRow3 = Cells(Rows.count, "F").End(xlUp).Row

        Set iRow12 = Range("P2:P" & LastRow3)         ' Ship Set

        With iRow12
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($P2<>"""",TRUE,FALSE)"    ' Ship Set
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 35
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow13 = Range("B1")

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""1320 EAST LOS ANGELES AVENUE,SHAFTER,CA,93263,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""10353 RICHMOND AVENUE,HOUSTON,TX,77042,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""19417 COLOMBO STREET,BAKERSFIELD,CA,93308,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""2040 OREGON STREET,ODESSA,TX,79764,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""1444 NORTH DERRICK DRIVE,CASPER,WY,82604,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""10906 FM 2920 ROAD,TOMBALL,TX,77375,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""21255 LA HIGHWAY 1 SOUTH,PLAQUEMINE,LA,70764,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""9870 EAST 30TH STREET,INDIANAPOLIS,IN,46229,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""8708 WEST LITTLE YORK,SUITE 100,HOUSTON,TX,77040,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow14 = Range("B2", Range("B2").End(xlDown))

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""1320 EAST LOS ANGELES AVENUE,SHAFTER,CA,93263,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""10353 RICHMOND AVENUE,HOUSTON,TX,77042,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""19417 COLOMBO STREET,BAKERSFIELD,CA,93308,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""2040 OREGON STREET,ODESSA,TX,79764,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""1444 NORTH DERRICK DRIVE,CASPER,WY,82604,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""10906 FM 2920 ROAD,TOMBALL,TX,77375,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""21255 LA HIGHWAY 1 SOUTH,PLAQUEMINE,LA,70764,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""9870 EAST 30TH STREET,INDIANAPOLIS,IN,46229,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""8708 WEST LITTLE YORK SUITE 100,HOUSTON,TX,77040,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        S = S & "Elaped time for Section 8 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Columns("O:O").Select
        Selection.TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
                                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                            :=Array(1, 7), TrailingMinusNumbers:=True    ' Ship Date

        Columns("T").Cut
        Columns("F").Insert Shift:=xlToRight

        Columns("AG").Cut                             ' Order Type
        Columns("R").Insert Shift:=xlToRight

        Columns("A:AH").EntireColumn.AutoFit

        Columns("A:B").ColumnWidth = 20               ' Customer
        Columns("G:G").ColumnWidth = 20               ' Description
        Columns("H:M").ColumnWidth = 9                ' Qty,Rsvd,OH,Avbl,PO/Req Qty,WIP


        Range("A1").Activate

        For Each w In Workbooks
            If w.Name Like "*FNDWRR*" Or w.Name Like "*3164*" Then
                Windows(w.Name).Activate
                ActiveWorkbook.Close Savechanges:=False
                Exit For
            End If
        Next w

        Sheets("Sheet1").Visible = False

    End If

    Application.PrintCommunication = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    S = S & "Elaped time for Section 5 = " & Timer - ST & vbCr    'for code execution timer
    MsgBox S
End Sub

THRASHER69 · Mar 26, 2020

Fluff said:
If you start of with 31,000 rows, the loops will definitely be slowing things down
At the moment you are deleting rows based on col AJ & from the sample of your data col Al has ready to be released for those rows that need to be kept.
Is this always the case?

No, that is not always the case. Column AI has 3 or 4 others besides ready to release. There is backordered, staged/pick confirmed and a couple of others I can't think of at the moment.

THRASHER69 · Mar 26, 2020

rlv01 said:

You should extend Fluff's example by methodically adding timer statements throughout your code to identify via measurement where it is slowing down. Example:

VBA Code:

Private Sub Import()

    Dim rng As Range
    Dim rg As Range
    Dim cond1 As FormatCondition
    Dim workrange As Range
    Dim Firstrow As Integer
    Dim LastRow As Integer
    Dim Lrow As Integer
    Dim LastRow1 As Long
    Dim LastRow2 As Long
    Dim LastRow3 As Long
    Dim i As Long
    Dim iRow As Range, cell As Range
    Dim iRow1 As Range
    Dim iRow2 As Range
    Dim iRow3 As Range
    Dim iRow4 As Range
    Dim iRow5 As Range
    Dim iRow6 As Range
    Dim iRow7 As Range
    Dim iRow8 As Range
    Dim iRow9 As Range
    Dim iRow10 As Range
    Dim iRow11 As Range
    Dim iRow12 As Range
    Dim iRow13 As Range
    Dim iRow14 As Range
    Dim r As Range


    Dim ST As Single, S As String                     'for code execution timer
    ST = Timer                                        'for code execution timer

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.PrintCommunication = False

    If WorksheetExists("Orders") Then
    Else


        Sheets("DO Ship Days").Visible = True
        Sheets.Add.Name = "Orders"
        Sheets("Orders").Move after:=Sheets("Sheet1")

        Sheets("Orders").Activate

        For Each w In Workbooks
            If w.Name Like "*FNDWRR*" Or w.Name Like "*3164*" Then
                Windows(w.Name).Activate
                Cells.Select
                Selection.Copy
                Windows("Oracle Orders.xlsm").Activate
                Cells.Select
                ActiveSheet.Paste
                Exit For
            End If
        Next w

        S = S & "Elaped time for Section 1 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer                                    'for code execution timer

        'Find first and last used row
        Range("AJ:AJ").Select                         ' Order Line Status
        Firstrow = ActiveSheet.UsedRange.Cells(1).Row
        LastRow = Cells(Rows.count, "AJ").End(xlUp).Row

        'Loop through used cells backwards and delete if needed
        For Lrow = LastRow To Firstrow Step -1
            Set workrange = Cells(Lrow, "AJ")
            If workrange.Value <> "Awaiting Fulfillment " _
               And workrange.Value <> "Awaiting Shipping" _
               And workrange.Value <> "Order Line Status" _
               And workrange.Value <> "Picked Partial  " _
               And workrange.Value <> "Closed " _
               And workrange.Value <> "Picked " _
               And workrange.Value <> "Production Open" _
               And workrange.Value <> "Production Partial" _
               And workrange.Value <> "Shipped" _
               And workrange.Value <> "Supply Eligible" _
               And workrange.Value <> "Ready To Release" _
               Then workrange.EntireRow.Delete
        Next Lrow


        S = S & "Elaped time for Section 2 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer


        LastRow3 = Cells(Rows.count, "E").End(xlUp).Row

        For Each r In Range("AL2:AL" & LastRow3)
            If r = "" Then r = r.Offset(, -2).Value
        Next

        ActiveSheet.Cells.Font.Name = "Kurale"
        ActiveSheet.Cells.Font.Size = 12


        Range("A:A,G:H,K:K,M:M,T:T,V:V,AB:AC,AM:AM,AO:AO,AR:AT,AV:BL").Delete

        Range("B1").Value = "Order#"
        Range("J1").Value = "Rsvd"
        Range("K1").Value = "OH"
        Range("L1").Value = "Avbl"
        Range("M1").Value = "PO/Req Qty"
        Range("N1").Value = "WIP"
        Range("S1").Value = "Ship Date"

        Sheets("Orders").AutoFilterMode = False
        Range("A1:AG1").AutoFilter
        Columns("A:AG").EntireColumn.AutoFit

        With Range("A:AG")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With

        S = S & "Elaped time for Section 3 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer


        Columns("D:E").Cut
        Columns("A").Insert Shift:=xlToRight

        Columns("C").Cut                              ' Order Manager
        Columns("AH").Insert Shift:=xlToRight

        Columns("F:M").Cut
        Columns("D").Insert Shift:=xlToRight

        Columns("Z").Cut                              ' Order Line Status
        Columns("L").Insert Shift:=xlToRight

        Columns("AB").Cut                             ' Shipping Status
        Columns("M").Insert Shift:=xlToRight

        Columns("T").Cut                              ' Ship Date
        Columns("N").Insert Shift:=xlToRight

        Columns("W").Cut                              ' Ship Set
        Columns("O").Insert Shift:=xlToRight

        Columns("AD").Cut
        Columns("P").Insert Shift:=xlToRight

        Columns("AC").Cut
        Columns("Q").Insert Shift:=xlToRight

        Columns("AE").Cut                             ' Unit Standard Cost
        Columns("Q").Insert Shift:=xlToRight

        Range("R1").Value = "Delivery#"
        Range("D1").Value = "Item#"

        Columns("Q").Insert Shift:=xlToRight

        Range("Q1").Value = "Line Total"


        S = S & "Elaped time for Section 4 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        LastRow2 = Cells(Rows.count, "A").End(xlUp).Row
        Range("Q2:Q" & LastRow2).Formula = "=SUM(P2*F2)"

        Range("A:B,E:E,L:M,U:AH").NumberFormat = "@"
        Range("C:D,F:K,O:O,S:T").NumberFormat = "0.00"
        Range("P:R").NumberFormat = "$#,##0.00"

        Columns("A:AH").EntireColumn.AutoFit

        Columns("A:B").ColumnWidth = 20
        Columns("E:E").ColumnWidth = 24
        Columns("L:L").ColumnWidth = 10

        Columns("A:B").HorizontalAlignment = xlLeft
        Columns("E:E").HorizontalAlignment = xlLeft

        Application.CutCopyMode = False

        ActiveWindow.FreezePanes = False
        Range("D2").Select
        ActiveWindow.FreezePanes = True

        Sheets("Orders").Activate

        Lrow = Range("A" & Rows.count).End(xlUp).Row
        Set rng = Range("A1:AH" & Lrow)

        With rng.Borders
            .LineStyle = xlContinuous
            .Color = vbBlack
            .Weight = xlThin
        End With

        Columns("Q").Cut                              ' Line Total
        Columns("D").Insert Shift:=xlToRight

        Sheets("Orders").AutoFilterMode = False
        Range("A1:AH1").AutoFilter

        Range("G:J").NumberFormat = "0"


        S = S & "Elaped time for Section 5 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Columns("G:G").TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True

        Columns("H:H").TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True

        Columns("I:I").TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True

        Columns("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True


        S = S & "Elaped time for Section 6 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Set iRow = Range("I2", Range("I2").End(xlDown))

        With iRow
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($I2<$G2,TRUE,FALSE)"    ' IF($OH < Qty ,TRUE,FALSE)
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = vbYellow
                    .TintAndShade = 0
                End With
            End With
        End With

        S = S & "Elaped time for Section 7 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Set iRow1 = Range("J2", Range("J2").End(xlDown))    'Avbl

        With iRow1
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($J2<$G2,TRUE,FALSE)"    ' IF(Avbl < Qty ,TRUE,FALSE)
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = vbYellow
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow2 = Range("C2", Range("C2").End(xlDown))

        With iRow2
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF(N2=""Released to Warehouse"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow3 = Range("E2", Range("E2").End(xlDown))    ' Item#

        With iRow3
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF(N2=""Released to Warehouse"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow4 = Range("N2", Range("N2").End(xlDown))

        With iRow4
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF(N2=""Released to Warehouse"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow5 = Range("C1")

        With iRow5
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(N:N,""Released to Warehouse"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow6 = Range("E1")

        With iRow6
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(N:N,""Released to Warehouse"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow7 = Range("D2", Range("D2").End(xlDown))

        With iRow7
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($N2=""Staged/Pick Confirmed"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 4
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow8 = Range("N2", Range("N2").End(xlDown))

        With iRow8
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($N2=""Staged/Pick Confirmed"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 4
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow9 = Range("C2", Range("C2").End(xlDown))

        With iRow9
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($O2=TODAY(),TRUE,FALSE)"    ' Ship Date
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 43
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow10 = Range("O2", Range("O2").End(xlDown))

        With iRow10
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($O2=TODAY(),TRUE,FALSE)"    ' Ship Date
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 43
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow11 = Range("A2", Range("A2").End(xlDown))

        With iRow11
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($P2<>"""",TRUE,FALSE)"    ' Ship Set
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 35
                    .TintAndShade = 0
                End With
            End With
        End With

        LastRow3 = Cells(Rows.count, "F").End(xlUp).Row

        Set iRow12 = Range("P2:P" & LastRow3)         ' Ship Set

        With iRow12
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($P2<>"""",TRUE,FALSE)"    ' Ship Set
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 35
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow13 = Range("B1")

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""1320 EAST LOS ANGELES AVENUE,SHAFTER,CA,93263,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""10353 RICHMOND AVENUE,HOUSTON,TX,77042,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""19417 COLOMBO STREET,BAKERSFIELD,CA,93308,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""2040 OREGON STREET,ODESSA,TX,79764,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""1444 NORTH DERRICK DRIVE,CASPER,WY,82604,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""10906 FM 2920 ROAD,TOMBALL,TX,77375,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""21255 LA HIGHWAY 1 SOUTH,PLAQUEMINE,LA,70764,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""9870 EAST 30TH STREET,INDIANAPOLIS,IN,46229,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""8708 WEST LITTLE YORK,SUITE 100,HOUSTON,TX,77040,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow14 = Range("B2", Range("B2").End(xlDown))

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""1320 EAST LOS ANGELES AVENUE,SHAFTER,CA,93263,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""10353 RICHMOND AVENUE,HOUSTON,TX,77042,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""19417 COLOMBO STREET,BAKERSFIELD,CA,93308,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""2040 OREGON STREET,ODESSA,TX,79764,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""1444 NORTH DERRICK DRIVE,CASPER,WY,82604,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""10906 FM 2920 ROAD,TOMBALL,TX,77375,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""21255 LA HIGHWAY 1 SOUTH,PLAQUEMINE,LA,70764,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""9870 EAST 30TH STREET,INDIANAPOLIS,IN,46229,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""8708 WEST LITTLE YORK SUITE 100,HOUSTON,TX,77040,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        S = S & "Elaped time for Section 8 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Columns("O:O").Select
        Selection.TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
                                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                            :=Array(1, 7), TrailingMinusNumbers:=True    ' Ship Date

        Columns("T").Cut
        Columns("F").Insert Shift:=xlToRight

        Columns("AG").Cut                             ' Order Type
        Columns("R").Insert Shift:=xlToRight

        Columns("A:AH").EntireColumn.AutoFit

        Columns("A:B").ColumnWidth = 20               ' Customer
        Columns("G:G").ColumnWidth = 20               ' Description
        Columns("H:M").ColumnWidth = 9                ' Qty,Rsvd,OH,Avbl,PO/Req Qty,WIP


        Range("A1").Activate

        For Each w In Workbooks
            If w.Name Like "*FNDWRR*" Or w.Name Like "*3164*" Then
                Windows(w.Name).Activate
                ActiveWorkbook.Close Savechanges:=False
                Exit For
            End If
        Next w

        Sheets("Sheet1").Visible = False

    End If

    Application.PrintCommunication = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    S = S & "Elaped time for Section 5 = " & Timer - ST & vbCr    'for code execution timer
    MsgBox S
End Sub

I will try this and see what I get. Thanks

THRASHER69 · Mar 26, 2020

rlv01 said:

You should extend Fluff's example by methodically adding timer statements throughout your code to identify via measurement where it is slowing down. Example:

VBA Code:

Private Sub Import()

    Dim rng As Range
    Dim rg As Range
    Dim cond1 As FormatCondition
    Dim workrange As Range
    Dim Firstrow As Integer
    Dim LastRow As Integer
    Dim Lrow As Integer
    Dim LastRow1 As Long
    Dim LastRow2 As Long
    Dim LastRow3 As Long
    Dim i As Long
    Dim iRow As Range, cell As Range
    Dim iRow1 As Range
    Dim iRow2 As Range
    Dim iRow3 As Range
    Dim iRow4 As Range
    Dim iRow5 As Range
    Dim iRow6 As Range
    Dim iRow7 As Range
    Dim iRow8 As Range
    Dim iRow9 As Range
    Dim iRow10 As Range
    Dim iRow11 As Range
    Dim iRow12 As Range
    Dim iRow13 As Range
    Dim iRow14 As Range
    Dim r As Range


    Dim ST As Single, S As String                     'for code execution timer
    ST = Timer                                        'for code execution timer

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.PrintCommunication = False

    If WorksheetExists("Orders") Then
    Else


        Sheets("DO Ship Days").Visible = True
        Sheets.Add.Name = "Orders"
        Sheets("Orders").Move after:=Sheets("Sheet1")

        Sheets("Orders").Activate

        For Each w In Workbooks
            If w.Name Like "*FNDWRR*" Or w.Name Like "*3164*" Then
                Windows(w.Name).Activate
                Cells.Select
                Selection.Copy
                Windows("Oracle Orders.xlsm").Activate
                Cells.Select
                ActiveSheet.Paste
                Exit For
            End If
        Next w

        S = S & "Elaped time for Section 1 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer                                    'for code execution timer

        'Find first and last used row
        Range("AJ:AJ").Select                         ' Order Line Status
        Firstrow = ActiveSheet.UsedRange.Cells(1).Row
        LastRow = Cells(Rows.count, "AJ").End(xlUp).Row

        'Loop through used cells backwards and delete if needed
        For Lrow = LastRow To Firstrow Step -1
            Set workrange = Cells(Lrow, "AJ")
            If workrange.Value <> "Awaiting Fulfillment " _
               And workrange.Value <> "Awaiting Shipping" _
               And workrange.Value <> "Order Line Status" _
               And workrange.Value <> "Picked Partial  " _
               And workrange.Value <> "Closed " _
               And workrange.Value <> "Picked " _
               And workrange.Value <> "Production Open" _
               And workrange.Value <> "Production Partial" _
               And workrange.Value <> "Shipped" _
               And workrange.Value <> "Supply Eligible" _
               And workrange.Value <> "Ready To Release" _
               Then workrange.EntireRow.Delete
        Next Lrow


        S = S & "Elaped time for Section 2 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer


        LastRow3 = Cells(Rows.count, "E").End(xlUp).Row

        For Each r In Range("AL2:AL" & LastRow3)
            If r = "" Then r = r.Offset(, -2).Value
        Next

        ActiveSheet.Cells.Font.Name = "Kurale"
        ActiveSheet.Cells.Font.Size = 12


        Range("A:A,G:H,K:K,M:M,T:T,V:V,AB:AC,AM:AM,AO:AO,AR:AT,AV:BL").Delete

        Range("B1").Value = "Order#"
        Range("J1").Value = "Rsvd"
        Range("K1").Value = "OH"
        Range("L1").Value = "Avbl"
        Range("M1").Value = "PO/Req Qty"
        Range("N1").Value = "WIP"
        Range("S1").Value = "Ship Date"

        Sheets("Orders").AutoFilterMode = False
        Range("A1:AG1").AutoFilter
        Columns("A:AG").EntireColumn.AutoFit

        With Range("A:AG")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With

        S = S & "Elaped time for Section 3 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer


        Columns("D:E").Cut
        Columns("A").Insert Shift:=xlToRight

        Columns("C").Cut                              ' Order Manager
        Columns("AH").Insert Shift:=xlToRight

        Columns("F:M").Cut
        Columns("D").Insert Shift:=xlToRight

        Columns("Z").Cut                              ' Order Line Status
        Columns("L").Insert Shift:=xlToRight

        Columns("AB").Cut                             ' Shipping Status
        Columns("M").Insert Shift:=xlToRight

        Columns("T").Cut                              ' Ship Date
        Columns("N").Insert Shift:=xlToRight

        Columns("W").Cut                              ' Ship Set
        Columns("O").Insert Shift:=xlToRight

        Columns("AD").Cut
        Columns("P").Insert Shift:=xlToRight

        Columns("AC").Cut
        Columns("Q").Insert Shift:=xlToRight

        Columns("AE").Cut                             ' Unit Standard Cost
        Columns("Q").Insert Shift:=xlToRight

        Range("R1").Value = "Delivery#"
        Range("D1").Value = "Item#"

        Columns("Q").Insert Shift:=xlToRight

        Range("Q1").Value = "Line Total"


        S = S & "Elaped time for Section 4 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        LastRow2 = Cells(Rows.count, "A").End(xlUp).Row
        Range("Q2:Q" & LastRow2).Formula = "=SUM(P2*F2)"

        Range("A:B,E:E,L:M,U:AH").NumberFormat = "@"
        Range("C:D,F:K,O:O,S:T").NumberFormat = "0.00"
        Range("P:R").NumberFormat = "$#,##0.00"

        Columns("A:AH").EntireColumn.AutoFit

        Columns("A:B").ColumnWidth = 20
        Columns("E:E").ColumnWidth = 24
        Columns("L:L").ColumnWidth = 10

        Columns("A:B").HorizontalAlignment = xlLeft
        Columns("E:E").HorizontalAlignment = xlLeft

        Application.CutCopyMode = False

        ActiveWindow.FreezePanes = False
        Range("D2").Select
        ActiveWindow.FreezePanes = True

        Sheets("Orders").Activate

        Lrow = Range("A" & Rows.count).End(xlUp).Row
        Set rng = Range("A1:AH" & Lrow)

        With rng.Borders
            .LineStyle = xlContinuous
            .Color = vbBlack
            .Weight = xlThin
        End With

        Columns("Q").Cut                              ' Line Total
        Columns("D").Insert Shift:=xlToRight

        Sheets("Orders").AutoFilterMode = False
        Range("A1:AH1").AutoFilter

        Range("G:J").NumberFormat = "0"


        S = S & "Elaped time for Section 5 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Columns("G:G").TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True

        Columns("H:H").TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True

        Columns("I:I").TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True

        Columns("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
                                     TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                     Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                                 :=Array(1, 1), TrailingMinusNumbers:=True


        S = S & "Elaped time for Section 6 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Set iRow = Range("I2", Range("I2").End(xlDown))

        With iRow
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($I2<$G2,TRUE,FALSE)"    ' IF($OH < Qty ,TRUE,FALSE)
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = vbYellow
                    .TintAndShade = 0
                End With
            End With
        End With

        S = S & "Elaped time for Section 7 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Set iRow1 = Range("J2", Range("J2").End(xlDown))    'Avbl

        With iRow1
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($J2<$G2,TRUE,FALSE)"    ' IF(Avbl < Qty ,TRUE,FALSE)
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = vbYellow
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow2 = Range("C2", Range("C2").End(xlDown))

        With iRow2
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF(N2=""Released to Warehouse"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow3 = Range("E2", Range("E2").End(xlDown))    ' Item#

        With iRow3
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF(N2=""Released to Warehouse"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow4 = Range("N2", Range("N2").End(xlDown))

        With iRow4
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF(N2=""Released to Warehouse"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow5 = Range("C1")

        With iRow5
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(N:N,""Released to Warehouse"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow6 = Range("E1")

        With iRow6
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(N:N,""Released to Warehouse"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 22
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow7 = Range("D2", Range("D2").End(xlDown))

        With iRow7
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($N2=""Staged/Pick Confirmed"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 4
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow8 = Range("N2", Range("N2").End(xlDown))

        With iRow8
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($N2=""Staged/Pick Confirmed"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 4
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow9 = Range("C2", Range("C2").End(xlDown))

        With iRow9
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($O2=TODAY(),TRUE,FALSE)"    ' Ship Date
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 43
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow10 = Range("O2", Range("O2").End(xlDown))

        With iRow10
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($O2=TODAY(),TRUE,FALSE)"    ' Ship Date
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 43
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow11 = Range("A2", Range("A2").End(xlDown))

        With iRow11
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($P2<>"""",TRUE,FALSE)"    ' Ship Set
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 35
                    .TintAndShade = 0
                End With
            End With
        End With

        LastRow3 = Cells(Rows.count, "F").End(xlUp).Row

        Set iRow12 = Range("P2:P" & LastRow3)         ' Ship Set

        With iRow12
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($P2<>"""",TRUE,FALSE)"    ' Ship Set
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 35
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow13 = Range("B1")

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""1320 EAST LOS ANGELES AVENUE,SHAFTER,CA,93263,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""10353 RICHMOND AVENUE,HOUSTON,TX,77042,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""19417 COLOMBO STREET,BAKERSFIELD,CA,93308,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""2040 OREGON STREET,ODESSA,TX,79764,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""1444 NORTH DERRICK DRIVE,CASPER,WY,82604,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""10906 FM 2920 ROAD,TOMBALL,TX,77375,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""21255 LA HIGHWAY 1 SOUTH,PLAQUEMINE,LA,70764,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""9870 EAST 30TH STREET,INDIANAPOLIS,IN,46229,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow13
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=COUNTIF(B:B,""8708 WEST LITTLE YORK,SUITE 100,HOUSTON,TX,77040,US"")"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        Set iRow14 = Range("B2", Range("B2").End(xlDown))

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""1320 EAST LOS ANGELES AVENUE,SHAFTER,CA,93263,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""10353 RICHMOND AVENUE,HOUSTON,TX,77042,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""19417 COLOMBO STREET,BAKERSFIELD,CA,93308,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""2040 OREGON STREET,ODESSA,TX,79764,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""1444 NORTH DERRICK DRIVE,CASPER,WY,82604,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""10906 FM 2920 ROAD,TOMBALL,TX,77375,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""21255 LA HIGHWAY 1 SOUTH,PLAQUEMINE,LA,70764,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""9870 EAST 30TH STREET,INDIANAPOLIS,IN,46229,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        With iRow14
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                                                "=IF($B2=""8708 WEST LITTLE YORK SUITE 100,HOUSTON,TX,77040,US"",TRUE,FALSE)"
            With .FormatConditions(.FormatConditions.count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .ColorIndex = 8
                    .TintAndShade = 0
                End With
            End With
        End With

        S = S & "Elaped time for Section 8 = " & Timer - ST & vbCr    'for code execution timer
        ST = Timer

        Columns("O:O").Select
        Selection.TextToColumns Destination:=Range("O1"), DataType:=xlDelimited, _
                                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
                                                                                            :=Array(1, 7), TrailingMinusNumbers:=True    ' Ship Date

        Columns("T").Cut
        Columns("F").Insert Shift:=xlToRight

        Columns("AG").Cut                             ' Order Type
        Columns("R").Insert Shift:=xlToRight

        Columns("A:AH").EntireColumn.AutoFit

        Columns("A:B").ColumnWidth = 20               ' Customer
        Columns("G:G").ColumnWidth = 20               ' Description
        Columns("H:M").ColumnWidth = 9                ' Qty,Rsvd,OH,Avbl,PO/Req Qty,WIP


        Range("A1").Activate

        For Each w In Workbooks
            If w.Name Like "*FNDWRR*" Or w.Name Like "*3164*" Then
                Windows(w.Name).Activate
                ActiveWorkbook.Close Savechanges:=False
                Exit For
            End If
        Next w

        Sheets("Sheet1").Visible = False

    End If

    Application.PrintCommunication = True
    Application.EnableEvents = True
    Application.DisplayStatusBar = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    S = S & "Elaped time for Section 5 = " & Timer - ST & vbCr    'for code execution timer
    MsgBox S
End Sub

Here is what the times looked like based on your code:

Elapsed time for Section 1 = 4.871094
Elapsed time for Section 2 = 492.6602
Elapsed time for Section 3 = 1.5
Elapsed time for Section 4 = 9.359375
Elapsed time for Section 5 = 2.34375
Elapsed time for Section 6 = 0.09375
Elapsed time for Section 7 = 3.90625E-03
Elapsed time for Section 8 = 0.0546875
Elapsed time for Section 5 = 3.09375

Fluff · Mar 26, 2020

Ok, thanks for that. Replace this

VBA Code:

        'Loop through used cells backwards and delete if needed
        For Lrow = LastRow To Firstrow Step -1
            Set workrange = Cells(Lrow, "AJ")
            If workrange.Value <> "Awaiting Fulfillment " _
               And workrange.Value <> "Awaiting Shipping" _
               And workrange.Value <> "Order Line Status" _
               And workrange.Value <> "Picked Partial  " _
               And workrange.Value <> "Closed " _
               And workrange.Value <> "Picked " _
               And workrange.Value <> "Production Open" _
               And workrange.Value <> "Production Partial" _
               And workrange.Value <> "Shipped" _
               And workrange.Value <> "Supply Eligible" _
               And workrange.Value <> "Ready To Release" _
               Then workrange.EntireRow.Delete
        Next Lrow

with

VBA Code:

      With Range("BL1:BL" & LastRow)
         .Formula = "=or(AJ1={""Awaiting Fulfillment "",""Awaiting Shipping"",""Order Line Status"",""Picked Partial  "",""Closed "",""Picked "",""Production Open"",""Production Partial"",""Shipped"",""Supply Eligible"",""Ready To Release""})"
         .AutoFilter 1, False
         .Parent.AutoFilter.Range.Offset(1).EntireRow.Delete
         .AutoFilter
         .ClearContents
      End With

which should speed things up.

THRASHER69 · Mar 26, 2020

Fluff said:

Ok, thanks for that. Replace this

VBA Code:

        'Loop through used cells backwards and delete if needed
        For Lrow = LastRow To Firstrow Step -1
            Set workrange = Cells(Lrow, "AJ")
            If workrange.Value <> "Awaiting Fulfillment " _
               And workrange.Value <> "Awaiting Shipping" _
               And workrange.Value <> "Order Line Status" _
               And workrange.Value <> "Picked Partial  " _
               And workrange.Value <> "Closed " _
               And workrange.Value <> "Picked " _
               And workrange.Value <> "Production Open" _
               And workrange.Value <> "Production Partial" _
               And workrange.Value <> "Shipped" _
               And workrange.Value <> "Supply Eligible" _
               And workrange.Value <> "Ready To Release" _
               Then workrange.EntireRow.Delete
        Next Lrow

with

VBA Code:

      With Range("BL1:BL" & LastRow)
         .Formula = "=or(AJ1={""Awaiting Fulfillment "",""Awaiting Shipping"",""Order Line Status"",""Picked Partial  "",""Closed "",""Picked "",""Production Open"",""Production Partial"",""Shipped"",""Supply Eligible"",""Ready To Release""})"
         .AutoFilter 1, False
         .Parent.AutoFilter.Range.Offset(1).EntireRow.Delete
         .AutoFilter
         .ClearContents
      End With

which should speed things up.

Wow, That made a big difference with that part. Thank you very much.

Elapsed time for Section 1 = 6.921875
Elapsed time for Section 2 = 8.929688
Elapsed time for Section 3 = 1.976563
Elapsed time for Section 4 = 16.94922
Elapsed time for Section 5 = 1.414063
Elapsed time for Section 6 = 0.1054688
Elapsed time for Section 7 = 0.1679688
Elapsed time for Section 8 = 6.640625E-02
Elapsed time for Section 5 = 2.808594

Fluff · Mar 26, 2020

Glad we could help & thanks for the feedback.
I think it will be difficult to make any more significant gains, without pulling the whole thing into an array & processing that.

VBA Help

THRASHER69

Board Regular

THRASHER69

Board Regular

Excel Facts

THRASHER69

Board Regular

Fluff

MrExcel MVP, Moderator

rlv01

Well-known Member

THRASHER69

Board Regular

THRASHER69

Board Regular

THRASHER69

Board Regular

Fluff

MrExcel MVP, Moderator

THRASHER69

Board Regular

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

VBA Help

Board Regular

Board Regular

Excel Facts

Board Regular

MrExcel MVP, Moderator

Well-known Member

Board Regular

Board Regular

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock