VBA Macro seems to freeze when ran with large dataset (NEW TO VBA)

Question

I'm new with writing macros. When I run my macro on big datasets, it seems it is freezing. I assume it's because of running through the dataset. Is there any way of fixing it or getting it to run smoothly?

    ' Second step, match accts that have holdings in the sell List and paste them to MasterSheets
     Sub testIt()
     Dim r As Long, endRow As Long, pasteRowIndex As Long
   pasteRowIndex = 1

For r = 6 To 100000

If Cells(r, Columns("C").Column).Value <> Empty Then
        'Copy the current row
        Rows(r).Select
        Selection.Copy
        'Switch to the sheet where you want to paste it & paste
        Sheets("Stocks to Sell").Select
        ActiveSheet.Rows(pasteRowIndex).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        'Next time you find a match, it will be pasted in a new row
        pasteRowIndex = pasteRowIndex + 1
       'Switch back to your table & continue to search for your criteria
        Sheets("Unrealized Gains Report").Select
End If
If Cells(r, Columns("D").Column).Value = "yes" Then 'Found
        'Copy the current row
        Rows(r).Select
        Selection.Copy
        'Switch to the sheet where you want to paste it & paste
        Sheets("Gmma Positions").Select
        ActiveSheet.Rows(pasteRowIndex).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        'Next time you find a match, it will be pasted in a new row
        pasteRowIndex = pasteRowIndex + 1
       'Switch back to your table & continue to search for your criteria
        Sheets("Unrealized Gains Report").Select
End If
Next r
End Sub

many threads here deal with optimisation. First stop, Nearly never a need to use Select!! https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — MacroMarc, Jun 19 '18 at 20:50

score 0 · Answer 1 · answered Jun 19 '18 at 21:48

0

As MacroMarc pointed out, Select is unnecessary and really slows this down. In addition, try rewriting the code to allow you to set destination cell value to source cell value instead of Copy.

answered Jun 19 '18 at 21:48

Rey Juna

347
2
12

score 0 · Answer 2 · answered Jun 20 '18 at 06:13

Add DoEvents in your loop.

https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/doevents-function

Any program which seems to freeze does this because it does not react to "requests" from outside. The user interface is not updated, the task manager shows "not responding", etc.

If you add DoEvents then your application will respond and it will update the user interface. So for you it does not seem frozen.

VBA Macro seems to freeze when ran with large dataset (NEW TO VBA)

2 Answers2