Vba- Macro running reallyyyy slowly- Can a guru find the cause?

[mnty89]

I have a workbook that contains this macro which basically takes a raw export on one sheet and then pastes it into an existing table and inputs the formulas that are in the columns that are calculated. Then the rows blank in row E are called to be deleted through the second sub macro. This locks up my computer for about 8 minutes sometimes and 4 minutes sometimes, but seems like it should be quite quick and simple.. Any idea what in here is causing this? I'm sure it's simple, but just being overlooked. Code below-


Sub Update_NSV_Data()


' Clear old from Data for new import


Application.ScreenUpdating = False
Application.DisplayAlerts = False


Sheets("NSV_Data").Select


Range("A3: AD100000").Select
Selection.ClearContents


Dim wrksht As Worksheet
Dim objListObj As ListObject

Set wrksht = ActiveWorkbook.Worksheets("NSV_Data")
Set objListObj = wrksht.ListObjects(1)

objListObj.Resize Range("A1:AD2")

' resets the table limits to only pull in data to pasted info in next step



' ActiveSheet.ListObjects("Data").Resize Range("$A$1:$AM3$")








Sheets("NSV_Pull").Select


' New Update for last row as of 2/24

Dim sht As Worksheet
Dim lastrow As Long
Set sht = ThisWorkbook.Worksheets("NSV_Pull")
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Range("A2:T" & lastrow).Select



Selection.Copy
Sheets("NSV_Data").Select
Range("A2").Select
ActiveSheet.Paste

Range("NSV[Master Customer]").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP([Sold-To Group],Vlook!C38:C[18],2,FALSE)"
Range("NSV[Turn Or Promo]").Select
ActiveCell.FormulaR1C1 = "=IF([Deal Number]=0,""TURN"",""PROMO"")"
Range("NSV[Category]").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP([Material UCC14],Vlook!C33:C36,2,FALSE),""Uncategorized"")"
Range("NSV[OPGI]").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR([Requested Delivery Date]-[Transit],[Requested Delivery Date])"
Range("NSV[Transit]").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP([Sold-To Party],Vlook!C29:C31,3,FALSE)"
Range("NSV[OPGI MONTH]").Select
ActiveCell.FormulaR1C1 = "=TEXT([OPGI],""mmmm"")"
Range("NSV[Pull Date]").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("NSV[DOW Create]").Select
ActiveCell.FormulaR1C1 = "=TEXT([PO Date],""dddd"")"
Range("NSV[Unique ID]").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE([Division],[Material UCC14],[Net Sale Value],[Order Quantity],[PO Date],[Sales Order Number],[Pull Date])"
Range("NSV[Return]").Select
ActiveCell.FormulaR1C1 = _
"=IF([Sales Order Number]>=60000000,""Return"",""Sales Order"")"




Range("U2:AD2").Select
Range("U2:AD2" & lastrow).FillDown


deleteBlankRows



Columns("A:A").Select
Selection.NumberFormat = "0"
Columns("B:B").Select
Selection.NumberFormat = "0"
Columns("C:C").Select
Selection.NumberFormat = "0"
Columns("E:E").Select
Selection.NumberFormat = "0"
Columns("G:G").Select
Selection.NumberFormat = "0.00"
Columns("H:H").Select
Selection.NumberFormat = "0.00"
Columns("I:I").Select
Selection.NumberFormat = "0"
Columns("J:J").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("K:K").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("L:L").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("M:M").Select
Selection.NumberFormat = "0"
Columns("N:N").Select
Selection.NumberFormat = "0"
Columns("O:O").Select
Selection.NumberFormat = "0"
Columns("P:P").Select
Selection.NumberFormat = "0"
Columns("R:R").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("X:X").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("Y:Y").Select
Selection.NumberFormat = "0"

Range("A1").Select

Sheets("Landing Page").Select
Range("A1").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox ("NSV_Data Updated")


End Sub


Sub deleteBlankRows()
On Error Resume Next
Columns("E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[GJSBED]

I'd say its the blank rows delete especially as their are 1,048,576 rows on a spreadsheet and most of them are probably blank. Try sorting the table so they are all at the bottom, that way you don't need to delete them.

 

[mnty89]

For anyone who finds this on google or has a similar issue.. I stepped through it on mine.. It was essentially freezing on the fill down command line. The formula auto populated anyway since it was in the first line of the table so simply commenting it out makes it run much faster.

Also added steps to turn calc mode to manual at start the back to automatic at end.