Macro Run Time

Viking1221

New Member
Joined
May 25, 2017
Messages
31
Hello,

I have this macro that is supposed to delete rows that are blank in either column C or D, but it tends to run for a long time and I am not sure why. Any ideas on how to make it more efficient?

Sub deleteBlankRows()
Range("D10:D8000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("C10:C8000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub


I also have a macro that delete row with all 0' in certain columns and it also seems to take a while to run. Not sure where I am going wrong:

Public Sub DeleteZeroRows()
Dim i As Long, lastrow As Long
lastrow = Range("D" & Rows.Count).End(xlUp).Row
For i = lastrow To 1 Step -1
If Application.Sum(Range("D" & i & ":BZ" & i)) = 0 Then
Rows(i).Delete
End If
Next i
End Sub

Thanks
 
Last edited:

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,865
.
Code:
Option Explicit


Sub deleteBlankRows()
On Error Resume Next
Range("C10:D8000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub


This macro will search Col D for cells containing a zero. Perhaps you can modify it to suit your needs of a range ?


Code:
Sub sbDelete_Rows_IF_Cell_Contains_Error()
Dim lRow As Long
Dim iCntr As Long
lRow = 8000
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 4) = 0 Then
        Rows(iCntr).Delete
    End If
Next
End Sub
 

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
You could attempt turning off resources you don't need while executing code.

Code:
Dim scrn As Boolean, Estate As Boolean, calc&
With Application    'get original settings
    scrn = .ScreenUpdating
    Estate = .EnableEvents
    calc = .Calculation
    'turn it off
    .ScreenUpdating = 0
    .EnableEvents = 0
    .Calculation = -4135
End With


'****your code here****




With Application    'turn it back on
    .Calculation = calc
    .EnableEvents = Estate
    .ScreenUpdating = scrn
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top