Slow code

[MichaelRSnow]

Hi, I'm filtering a spreadsheet using VBA inside a module (spreadsheet holds 50k+ rows of data) and these steps below take 7 minutes to execute?

Any advice on how this could be done differently to drastically speed up run time?

'Get all products
ThisWorkbook.Sheets("Prod_Data").Range("A1:BE1").AutoFilter Field:=Range("P:P").Column
ThisWorkbook.Sheets("Prod_Data").Range("A1:BE1").AutoFilter Field:=Range("Q:Q").Column
ThisWorkbook.Sheets("Prod_Data").Range("W1:W150000").SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Sheets("Prod_Data_Rsts").Range("M1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
Application.CutCopyMode = False
    
ThisWorkbook.Sheets("Prod_Data").Range("P1:P150000").SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Sheets("Prod_Data_Rsts").Range("N1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
Application.CutCopyMode = False

ThisWorkbook.Sheets("Prod_Data").Range("D1:D150000").SpecialCells(xlCellTypeVisible).Copy
ThisWorkbook.Sheets("Prod_Data_Rsts").Range("O1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
Application.CutCopyMode = False

With ActiveWorkbook.Worksheets("Prod_Data_Rsts").ListObjects("Table1").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("Table1[Product Start Date]"), SortOn:=xlSortOnValues, Order:=xlAscending
       .Header = xlYes
       .Apply
End With

 

[MichaelRSnow]

1. You didn't put the full macro.

2. If copying information from sheet "Prod_Data" to sheet "Prod_Data_Rsts" and then you have formulas that use data from sheet "Prod_Data_Rsts" to bring other data from sheet "Prod_Data", the ideal is for the macro to collect the data from sheet 1 and put them together with the copied data. That is, the formulas would not be necessary.


3. And speaking of formulas, I suppose that in sheet "Prod_Data_Rsts" you have 50 thousand rows with formulas waiting for the information from sheet "Prod_Data". And probably only 1000 rows are used and you'll have 49 thousand rows with unused formulas.
That doesn't help the blade's performance. Plus you have the formulas in a table. I also recommend that you put the data in cells. And remove the rows with formulas you don't use.

4. In addition to the "FormR" lines, I recommend the following.

Sub test()

  With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
  End With

  'Get all products
  ThisWorkbook.Sheets("Prod_Data").Range("A1:BE1").AutoFilter Field:=Range("P:P").Column
  ThisWorkbook.Sheets("Prod_Data").Range("A1:BE1").AutoFilter Field:=Range("Q:Q").Column
  ThisWorkbook.Sheets("Prod_Data").Range("W1:W150000").SpecialCells(xlCellTypeVisible).Copy
  ThisWorkbook.Sheets("Prod_Data_Rsts").Range("M1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
      False, Transpose:=False
  Application.CutCopyMode = False
  
  ThisWorkbook.Sheets("Prod_Data").Range("P1:P150000").SpecialCells(xlCellTypeVisible).Copy
  ThisWorkbook.Sheets("Prod_Data_Rsts").Range("N1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
      False, Transpose:=False
  Application.CutCopyMode = False
 
  ThisWorkbook.Sheets("Prod_Data").Range("D1:D150000").SpecialCells(xlCellTypeVisible).Copy
  ThisWorkbook.Sheets("Prod_Data_Rsts").Range("O1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
      False, Transpose:=False
  Application.CutCopyMode = False
 
  With ActiveWorkbook.Worksheets("Prod_Data_Rsts").ListObjects("Table1").Sort
  .SortFields.Clear
  .SortFields.Add Key:=Range("Table1[Product Start Date]"), SortOn:=xlSortOnValues, Order:=xlAscending
         .Header = xlYes
         .Apply
  End With
 
 
  With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
  End With
 
End Sub

5. And last, if you opt for a new macro, put here a sample of your data from the 2 sheets, change the sensitive information for generic data.
Or share a file.
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Thanks all

That has worked perfectly, reducing code from 9m 56s to 36s, amazing improvement

Improvements below, new vs previous. Thanks all

00:00:03​	00:00:08​
00:00:03​	00:00:08​
00:00:02​	00:00:19​
00:00:03​	00:00:19​
00:00:03​	00:00:17​
00:00:03​	00:00:22​
00:00:02​	00:00:14​
00:00:03​	00:00:36​
00:00:03​	00:01:01​
00:00:03​	00:06:02​
00:00:03​	00:00:10​
00:00:02​	00:00:13​
00:00:03​	00:00:07​
0:00:36​	0:09:56​