Deleting rows bogging down code vba

Question

The below code is causing my code to bog down for about 15 seconds while it's running. Does anyone have any suggestions that would speed this up?

Thanks,

Range("Test_Range").Offset(1, 1).Activate
Do Until ActiveCell.Offset(0, -1) = ""
    If ActiveCell.Value <= 0.01 Then
        Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 8)).Delete Shift:=xlUp
        ActiveCell.Offset(-1, 0).Activate
    Else
    End If
    ActiveCell.Offset(1, 0).Activate
Loop

1) try to avoid using `Activate` statement (read [this](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select), please). 2) try to add `Application.ScreenUpdating=False` in the very beggining of your code, and `Application.ScreenUpdating=True` after your code. Try to use `With` statement and use `Range` object instead `ActiveCell` — Dmitry Pavliv, Jan 22 '14 at 18:31

simpLE MAn · Accepted Answer · 2014-01-22T20:01:39.373

I would do it like this:

'***This code is to be inserted within the coding module
'of the sheet to be modified

Dim calcDefaultState As XlCalculation
'To retain the current XlCalculation property
calcDefaultState = Application.Calculation

'to speed up the process
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim lastRow As Long
'To find the last non empty row of "Test_Range" column
lastRow = Me.Cells(Me.Rows.Count, Range("Test_Range").Column).End(xlUp).Row

Dim i As Long: i = 1
Do Until i = (lastRow - Range("Test_Range").Row) + 1
    With Range("Test_Range").Offset(i, 1)
        If .Value <= .01 Then
            Me.Range(Cells(.Row, 1), Cells(.Row, 8)).Delete Shift:=xlUp
            lastRow = lastRow - 1
        Else
            i = i + 1
        End If
    End With
Loop

'To put back the original XlCalculation property
Application.Calculation = calcDefaultState
Application.ScreenUpdating = True

Note that if there is a slight chance that the last row of your excel file can be non-empty, you should add a check to verify it because in that case the lastRow won't be accurate.

Hi simpLE MAn, I'm getting an invalid use of the Me keyword? I've never encountered that error before. — ye-olde-dev, Jan 22 '14 at 20:45
@Clouse24, did you put the code within the sheet's code module or in a module? — simpLE MAn, Jan 22 '14 at 21:02

score 0 · Answer 2 · answered Jan 22 '14 at 18:23

0

The code works perfectly and speedy on my computer. Assuming no performing issue with your computer (ie. HDD full / out of memory), could it be the range that is being deleted actually involves in other spreadsheet calculation?

answered Jan 22 '14 at 18:23

Alex

1,632
1
12
28

Thanks Alex, it's part of a very large block of code. Essentially, I've pulled together some values from a large array and dumped them into a range. I'd like to get ride of the results that are less than 1%. I don't have any calculations on the spreadsheet at all as they're all performed within VBA. Normally I'd just use special cell type and delete the row but there is another table on that row elsewhere on the spreadsheet – ye-olde-dev Jan 22 '14 at 19:00

Deleting rows bogging down code vba

2 Answers2