Why is my file so big and marco so slow??? - link to file

[wanttolearnmore]

Everyone is so helpful on here!

I'm sure I must have done something wrong.....my file has become rather large (and slow to open, run etc) (link below). Hopefully there's just a few small changes to make

I think it's fairly self explanitory what I'm trying to do, if not please ask.

http://www.mediafire.com/?eiyboyop7le942e


My code is as follows

Sub promolookup()
Application.ScreenUpdating = False
Sheets("upload data").Columns("A:A").Insert Shift:=xlToRight
Sheets("upload data").Range("A1").FormulaR1C1 = "Lookup"
With Worksheets("UPLOAD DATA")
Lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
If Lastrow > 2 Then
Range("A2").FormulaR1C1 = "=RC[1]&RC[2]&RC[4]&RC[5]"
Range("A2").AutoFill Destination:=Range("a2:a" & Range("b2").End(xlDown).Row), Type:=xlFillDefault
Range("a2:a" & Range("b2").End(xlDown).Row).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


End If
End With

Sheets("data input").Select
Range(Range("promo").Value).Copy
Range("f7").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("h:i").EntireColumn.Hidden = True
Range("j7").FormulaR1C1 = "Allocation"
Range("k7").FormulaR1C1 = "Sold"


Range("b7:b" & Range("f7").End(xlDown).Row).FormulaArray = "=monthnumber"
Range("c7:c" & Range("f7").End(xlDown).Row).FormulaArray = "=storename"
Range("d7:d" & Range("f7").End(xlDown).Row).FormulaArray = "=ponumber"
Range("e7:e" & Range("f7").End(xlDown).Row).FormulaArray = "=promoname"

Range("A7").FormulaR1C1 = "=RC[1]&RC[2]&RC[4]&RC[5]"
Range("A7").AutoFill Destination:=Range("a7:a" & Range("f7").End(xlDown).Row), Type:=xlFillDefault


Range("a7:e" & Range("a7").End(xlDown).Row).Select



Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("K8").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],'Upload Data'!C[-10]:C[-3],8,FALSE)"
Range("K8").Select
Selection.AutoFill Destination:=Range("K8:K270"), Type:=xlFillDefault
Range("K8:K270").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#n/a", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("G8").Select
Application.CutCopyMode = False
Sheets("data input").Range("m7").FormulaR1C1 = "Total Allocated"
Sheets("data input").Range("N7").FormulaR1C1 = "Total Sold"
Sheets("data input").Range("O7").FormulaR1C1 = "Left To Sell"


Sheets("Upload Data").Range("I2").FormulaR1C1 = "=RC[-4]&RC[-3]"
With Worksheets("UPLOAD DATA")
Lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
If Lastrow > 2 Then
Range("I2").AutoFill Destination:=Range("I2:i" & Range("a2").End(xlDown).Row)
End If
End With
Sheets("data input").Range("L8").FormulaR1C1 = "=promoname&RC[-6]"
Sheets("data input").Range("L8").AutoFill Destination:=Range("L8:L" & Range("a8").End(xlDown).Row)

Sheets("data input").Range("N8").FormulaR1C1 = _
"=SUMIF('Upload Data'!C[-5],'Data Input'!RC[-2],'Upload Data'!C[-6])"
Sheets("data input").Range("N8").AutoFill Destination:=Range("n8:n" & Range("a8").End(xlDown).Row)
Sheets("data input").Range("O8").FormulaR1C1 = "=RC[-2]-RC[-1]"
Sheets("data input").Range("O8").AutoFill Destination:=Range("o8" & Range("a8").End(xlDown).Row)

Sheets("data input").Range("n8" & Range("a8").End(xlDown).Row).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Upload Data").Columns("I:I").Delete Shift:=xlToLeft
Sheets("data input").Columns("L:L").ClearContents
Sheets("data input").Columns("M:O").EntireColumn.AutoFit
Sheets("data input").Columns("N:O").Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Upload Data").Columns("G:G").EntireColumn.AutoFit
Range("k9").Select
Application.ScreenUpdating = True

End Sub

Sub submit()

Application.ScreenUpdating = False

Range("k7").ClearContents
Range("k7:k300").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Columns("h:j").Select
Selection.Delete Shift:=xlToLeft
Sheets("Upload Data").Range("I2").FormulaR1C1 = "=VLOOKUP(RC[-8],'Data Input'!C[-8]:C[-1],8,FALSE)"
With Worksheets("UPLOAD DATA")
Lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
If Lastrow > 2 Then
Range("I2").AutoFill Destination:=Range("I2:i" & Range("a2").End(xlDown).Row)
End If

End With
Sheets("UPLOAD DATA").Select
Rows("1:1").Select
Range("E1").Activate
Selection.AutoFilter
Range("J9").Select
ActiveSheet.Range("i2:i" & Range("a2").End(xlDown).Row).AutoFilter Field:=9, Criteria1:="<>#n/a", _
Operator:=xlAnd
Range("i2:i" & Range("a7").End(xlDown).Row).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
Selection.AutoFilter
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Sheets("Data Input").Range("a7:h" & Range("b7").End(xlDown).Row).Copy
With Worksheets("upload data")
.Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial
End With
Application.CutCopyMode = False
Sheets("Data Input").Range("a7:h" & Range("g7").End(xlDown).Row).ClearContents
Sheets("Data Input").Select
Application.ScreenUpdating = True

End Sub

 

[AlphaFrog]

Hi and welcome.

Excel thinks your sheet "Upload Data" is full of data even though it isn't. Notice the size of the scroll bar on the right when sheet "Upload Data" is selected.

Read this: How to reset the last cell in Excel

• Select sheet "Upload Data"
• Select cell A2
• Ctrl+Shift+End
• Select from the menu Edit\ Clear\ All
• Save your workbook.

I didn't look through the macro. You may or may not have to make changes there as well.

 

[wanttolearnmore]

Thank you for your help, I reset the last cell however each time I run the macro it creates the max number of rows again.....I must have to change my code somewhere?