PDA

View Full Version : How to speed up my macro?



Filribeiro
04-24-2015, 02:53 AM
Hi guys,


I'm very inexperienced in programming but after googling a bit, i made a macro to manipulate my excel spreadsheet.


I have 2 questions:


1- Is it possible to speed up this kind of macros with a different type of code/functions?


2- Most of the times this macro run fast, but sometimes is can get VERY slow... Do you have an idea why?


Here is the code of the Macro:


Private Sub Image1_Click()


Application.Calculation = xlCalculationManual
ActiveSheet.Calculate
Application.ScreenUpdating = False
Application.EnableEvents = False


Set Calculos = Sheets("SAPATAS")


ncombinacoes = Calculos.Cells(1, 1)


n = ncombinacoes


'Insert Lines


ActiveSheet.Range(Cells(22, 1), Cells(22 + n - 4, 1)).EntireRow.Select
ActiveSheet.Range(Cells(22, 1), Cells(22 + n - 4, 1)).EntireRow.Insert


'Copy for n combinations
'___Zone 1


ActiveSheet.Range(Cells(20, 2), Cells(20, 10)).Select
ActiveSheet.Range(Cells(20, 2), Cells(20, 10)).Copy
ActiveSheet.Range(Cells(21, 2), Cells(21 + n - 1, 10)).Select
ActiveSheet.Paste


'___Zone 2


ActiveSheet.Range(Cells(21, 11), Cells(21, 12)).Select
ActiveSheet.Range(Cells(21, 11), Cells(21, 12)).Copy
ActiveSheet.Range(Cells(22, 11), Cells(22 + n - 2, 12)).Select
ActiveSheet.Paste


'___Zone 3


ActiveSheet.Range(Cells(20, 13), Cells(20, 17)).Select
ActiveSheet.Range(Cells(20, 13), Cells(20, 17)).Copy
ActiveSheet.Range(Cells(21, 13), Cells(21 + n - 1, 17)).Select
ActiveSheet.Paste


'___Zone 4


ActiveSheet.Range(Cells(21, 18), Cells(21, 24)).Select
ActiveSheet.Range(Cells(21, 18), Cells(21, 24)).Copy
ActiveSheet.Range(Cells(22, 18), Cells(22 + n - 2, 24)).Select
ActiveSheet.Paste


'___Zone 5


ActiveSheet.Range(Cells(20, 25), Cells(20, 47)).Select
ActiveSheet.Range(Cells(20, 25), Cells(20, 47)).Copy
ActiveSheet.Range(Cells(21, 25), Cells(21 + n - 1, 47)).Select
ActiveSheet.Paste


'___Zone 6


ActiveSheet.Range(Cells(20, 57), Cells(20, 70)).Select
ActiveSheet.Range(Cells(20, 57), Cells(20, 70)).Copy
ActiveSheet.Range(Cells(21, 57), Cells(21 + n - 1, 70)).Select
ActiveSheet.Paste


'___Zone 7


ActiveSheet.Range(Cells(20, 77), Cells(20, 77)).Select
ActiveSheet.Range(Cells(20, 77), Cells(20, 77)).Copy
ActiveSheet.Range(Cells(21, 77), Cells(21 + n - 1, 77)).Select
ActiveSheet.Paste


'___Grouping


ActiveSheet.Range(Cells(21, 1), Cells(21 + n - 1, 1)).EntireRow.Select
Selection.Rows.Group
ActiveSheet.Range(Cells(21, 1), Cells(21 + n - 1, 1)).EntireRow.Select


'___Copy for all the items


nsapatas = Calculos.Cells(2, 1)


k = 100


ActiveSheet.Range(Cells(20, 1), Cells(20 + n - 1, k)).EntireRow.Select
ActiveSheet.Range(Cells(20, 1), Cells(20 + n - 1, k)).EntireRow.Copy
ActiveSheet.Range(Cells(20 + n, 1), Cells(20 + n + n - 1, k)).EntireRow.Select
ActiveSheet.Paste


For p = 3 To nsapatas


Selection.Copy
ActiveCell.Offset(n, 0).EntireRow.Select
ActiveSheet.Paste


Next p


ActiveSheet.Outline.ShowLevels RowLevels:=1
Application.Calculation = xlCalculationAutomatic
ActiveSheet.Calculate
Application.ScreenUpdating = True
Application.EnableEvents = True


End Sub


Thanks in advance!


FR

jonh
04-24-2015, 07:04 AM
To start with, remove all of the .select lines.
You don't need to select something in order to perform an action on it.

i.e.

ActiveSheet.Range(Cells(21, 1), Cells(21 + n - 1, 1)).EntireRow.Select
Selection.Rows.Group

might become

ActiveSheet.Range(Cells(21, 1), Cells(21 + n - 1, 1)).EntireRow.Rows.Group

Filribeiro
04-24-2015, 08:15 AM
To start with, remove all of the .select lines.
You don't need to select something in order to perform an action on it.

i.e.

ActiveSheet.Range(Cells(21, 1), Cells(21 + n - 1, 1)).EntireRow.Select
Selection.Rows.Group

might become

ActiveSheet.Range(Cells(21, 1), Cells(21 + n - 1, 1)).EntireRow.Rows.Group


Thanks for the tip ! :)