Macro running slow

**rz6657** · 11-14-2012, 02:23 PM

I created a macro but it runs slow. Please can someone review the macro and recommend something?

The macro is in module 1 in the attached file. Thanks.

**HaHoBe** · 11-14-2012, 02:34 PM

Hi, vrz6657,

using the macro recorder gives code that needs to be reworked.

Try this one:

Sub Macro3()
'
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    Dim StartSheet As String
    StartSheet = ActiveSheet.Name


    
    Sheets(StartSheet).Select
'    ActiveWindow.SmallScroll Down:=9
'    Range("G48:R51").Select
'    ActiveWindow.SmallScroll Down:=15
'    Range("G48:R51,G54:R57").Select
'    Range("G54").Activate
'    ActiveWindow.SmallScroll Down:=24
'    Range("G48:R51,G54:R57,G76:R85").Select
'    Range("G76").Activate
'    ActiveWindow.SmallScroll Down:=15
'    Range("G48:R51,G54:R57,G76:R85,G91:R100").Select
'    Range("G91").Activate
'    ActiveWindow.SmallScroll Down:=24
'    Range( _
'        "G48:R51,G54:R57,G76:R85,G91:R100,G109:R113,G116:R116,G119:R119,G127:R131"). _
'        Select
'    Range("G127").Activate
'    ActiveWindow.SmallScroll Down:=12
    With Range( _
        "G48:R51,G54:R57,G76:R85,G91:R100,G109:R113,G116:R116,G119:R119,G127:R131,G134:R134,G137:R137" _
        )
'    Range("G137").Activate
'    ActiveWindow.SmallScroll Down:=-87
      .Replace What:="$F45", Replacement:="$D45", LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
          ReplaceFormat:=False
    
'    Sheets(StartSheet).Select
'    ActiveWindow.SmallScroll Down:=9
'    Range("G48:R51").Select
'    ActiveWindow.SmallScroll Down:=15
'    Range("G48:R51,G54:R57").Select
'    Range("G54").Activate
'    ActiveWindow.SmallScroll Down:=24
'    Range("G48:R51,G54:R57,G76:R85").Select
'    Range("G76").Activate
'    ActiveWindow.SmallScroll Down:=15
'    Range("G48:R51,G54:R57,G76:R85,G91:R100").Select
'    Range("G91").Activate
'    ActiveWindow.SmallScroll Down:=24
'    Range( _
'        "G48:R51,G54:R57,G76:R85,G91:R100,G109:R113,G116:R116,G119:R119,G127:R131"). _
'        Select
'    Range("G127").Activate
'    ActiveWindow.SmallScroll Down:=12
'    Range( _
'        "G48:R51,G54:R57,G76:R85,G91:R100,G109:R113,G116:R116,G119:R119,G127:R131,G134:R134,G137:R137" _
'        ).Select
'    Range("G137").Activate
'    ActiveWindow.SmallScroll Down:=-87
        .Replace What:="$E45", Replacement:="$D45", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
'    Sheets(StartSheet).Select
    .Range("C41").Select
  End With


'To update BEA Dump
    
    Sheets("BEA Dump").Select
    
    Dim rcell As Range
    Dim lr As Long

    lr = Cells(Rows.Count, 2).End(xlUp).Row

'    For Each rcell In Range("N5:N" & lr)
'
'      If Left(rcell, 3) = "ITO" Then
'
'          rcell.Offset(, -1).Value = "All"
'
'      Else
'
'          rcell.Offset(, -1).Value = "All"
'
'      End If
'
'    Next rcell
    Range("N5:N" & lr).Offset(, -1).Value = "All"
    Sheets(StartSheet).Select
'    ActiveWindow.SmallScroll Down:=-87
    Range("C41").Select


    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    
    
    
End Sub

Ciao,
Holger

**rz6657** · 11-14-2012, 03:10 PM

Holger - thanks for your feedback. That is one of my macros that has "All" in both instances. My other has if ITO = ITO if not = NITO. Will that change you did also work for that other macro?

**HaHoBe** · 11-14-2012, 04:42 PM

Hi, rz6657,

the change I made is just for the case that one item will be filled in. If that differs you need to loop through the range just like you did in the original code or maybe think about using the Autofilter and work on the visible cells thereafter.

Ciao,
Holger