MichaelRSnow
Active Member
- Joined
- Aug 3, 2010
- Messages
- 409
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?
Any advice on how this could be done differently to drastically speed up run time?
VBA Code:
'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