CODEWITHSUNDEEP
excelloopsvba
plankton
plankton

Reputation: 325

Excel VBA Macro Not Responding After Running Over Night

I built a random number generator in excel which outputs to another column in another sheet in the workbook. The macro has a cut off at cell A1,000,000. When I run it throughout the day while I'm working I can get close to 200,000 rows of output. When I run it over night and come back in the morning, it's frozen (Not Responding) which I think means it just worked itself into freezing before hitting the cut off.

I took a look at some other posts but they didn't quite answer my questions (Excel Not Responding During Macro) (Excel not responding after running macro).

Is there any way I can get it out of Not Responding and just disable the macro and take a look at the output?

Why is this happening?

Here is my code:

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'

Do
    Do

      Do
      Range("H12").Select
      Range("H12").ClearContents

      Loop Until Range("K10") = "MATCH" And Range("K11") = "GOOD"

      Range("H2:H8").Select
      Selection.Copy
      Range("P1").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,    SkipBlanks _
      :=False, Transpose:=False
      Range("P1:P5").Select
      Application.CutCopyMode = False
      ActiveWorkbook.Worksheets("NUMBER GENERATOR").sort.SortFields.Clear
      ActiveWorkbook.Worksheets("NUMBER GENERATOR").sort.SortFields.Add Key:=Range( _
      "P1:P5"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
      xlSortNormal
  With ActiveWorkbook.Worksheets("NUMBER GENERATOR").sort
      .SetRange Range("P1:P5")
      .Header = xlGuess
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
  End With


    Loop Until Range("P11") = "GOOD" And Range("P12") = 1


 Range("P9").Select
 Selection.Copy


 Sheets("Sheet1").Select
 Sheets("Sheet1").Range("A1").Select
 Range("A" & Rows.Count).End(xlUp).Offset(1).Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,    Transpose:=False


 Sheets("NUMBER GENERATOR").Select
 Range("H12").Select

Loop Until Sheets("Sheet1").Range("A1000000") <> ""

End Sub

Upvotes: 2

Views: 480

Answers (1)

John Coleman
John Coleman

Reputation: 51998

Select is almost never needed in VBA. For example, the two lines

Range("H12").Select
Range("H12").ClearContents

can be replaced simply by:

Range("H12").ClearContents

More importantly, the 7 lines

Range("H2:H8").Select
Selection.Copy
Range("P1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,    SkipBlanks _
  :=False, Transpose:=False
Range("P1:P5").Select
Application.CutCopyMode = False

can be replaced by the single line

Range("P1:P7").Value = Range("H2:H8").Value

Similar remarks hold for other uses of Select. Making these changes and turning off screen-updating while the macro is running should help substantially (whether or not it will help enough is hard to say since it isn't clear what you are actually doing).

Upvotes: 3

Related Questions