Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
how to make macro faster
#1
I have this macro and takes 20 minutes to run and freezes MS Excel. Can someone take a look at this and help. Thanks



Sub formatdata()

Application.ScreenUpdating = False

Sheets("CDW_RawData").Select

Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Cells.Select
With Selection
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("DBig Grin").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("G:G").ColumnWidth = 21.57
Columns("G:G").ColumnWidth = 28.57
Columns("G:G").ColumnWidth = 35
Columns("H:H").ColumnWidth = 10.57
Columns("I:I").ColumnWidth = 15.57
Cells.Select
Cells.EntireRow.AutoFit
Range("F13").Select

Dim Y As Variant

Y = 4

Do Until Y = 1000
If Cells(Y, 7) <> "" And Cells(Y, 4) = "" And Cells(Y, 1) = "" Then
Cells(Y - 1, 4).Select
Selection.Copy
Cells(Y, 4).Select
ActiveSheet.Paste
End If
Y = Y + 1
Loop

Dim Z As Variant

Z = 4

Do Until Z = 1000
If Cells(Z, 7) <> "" And Cells(Z, 8) = "" And Cells(Z, 1) = "" Then
Cells(Z - 1, 8).Select
Selection.Copy
Cells(Z, 8).Select
ActiveSheet.Paste
End If
Z = Z + 1
Loop

Columns("DBig Grin").Select
Application.CutCopyMode = False
Selection.Cut
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("E1").Select

Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Range("G1").Select
Range("G1") = "Left trim"

Dim G As Variant
G = 4
Do Until G = 1000
Cells(G, 7) = "=LEFT(RC[-1],4)"
G = G + 1
Loop

Columns("A:AA").EntireColumn.AutoFit

Sheets("CDW_467's").Select
Sheets("CDW_SLR_740's").Select

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Range("A1") = "Account Number"

Dim E As Variant
E = 2
Do Until E = 1000
Cells(E, 6) = "=LEFT(RC[-3],4)"
E = E + 1
Loop

End Sub
Sub lookupinformation()
Application.ScreenUpdating = False
Dim X As Variant
Dim Y As Variant
Dim M As Variant
Dim abc As String

Sheets("CDW_467's").Select
Sheets("CDW_SLR_740's").Select

Range("F2:F33761").Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C2").Select
Application.CutCopyMode = False
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Range("E2").Select

X = 2
Y = 4
M = 2

Do Until X = 770
Sheets("CDW_467's").Select
Sheets("CDW_SLR_740's").Select
Cells(X, 3).Select
abc = Cells(X, 3)

Do Until Y = 770
Sheets("CDW_RawData").Select
If Cells(Y, 7) = abc And Cells(Y, 9) <> "Inactive" Then
Cells(Y, 8).Select
Selection.Copy
Sheets("CDW_467's").Select
Sheets("CDW_SLR_740's").Select
Cells(M, 1).Select
ActiveSheet.Paste
End If
Y = Y + 1
Loop
Y = 4
abc = 0
M = M + 1
X = X + 1
Loop



End Sub

Sub runallmacros()

Call formatdata
Call lookupinformation
End Sub
#2
Use Visual Basic for Applications (VBA). A VBA editor is is part of the Excel application.


Forum Jump:


Users browsing this thread: 1 Guest(s)