Speed up deleting process?

C

carddard

Active Member
Joined
Aug 19, 2008
Messages
427
Hi there, is there anyway this process could be sped up?
Code: 
Sub DeleteDataPastDate()
Dim i As Long, lastrow As Long
lastrow = Range("A65536").End(xlUp).Row
Sheets("Heldsec").Select
For i = lastrow To 2 Step -1
    If Cells(i, 5) = "X" And Cells(i, 8) <> Date - 1 Then
    Cells(i, 1).EntireRow.Delete
    End If
    
    If Cells(i, 5) = "W" And Cells(i, 8) <> Date - 1 Then
    Cells(i, 1).EntireRow.Delete
    End If
    
    If Cells(i, 5) = "Q" And Cells(i, 8) <> Date - 1 Then
    Cells(i, 1).EntireRow.Delete
    End If
    
    If Cells(i, 5) = "" And Cells(i, 8) <> Date - 1 Then
    Cells(i, 1).EntireRow.Delete
    End If
    
Next
End Sub
All help is appreciated.
 
Sort by date Sort by votes
This doesn't use looping
Code: 
With ThisWorkbook.Sheets("Heldsec").UsedRange
    With .Parent.Cells(2, .Column + .Columns.Count).Resize(.Rows.Count, 1)
        .FormulaR1C1 = "=1/AND(OR(EXACT(RC5,""W""),EXACT(RC5,""X""),EXACT(RC5,""Q""),EXACT(RC5,"""")),RC8<>TODAY()-1)"
        Calculate
        On Error Resume Next
        .SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow.Delete
        On Error GoTo 0
        .EntireColumn.Clear
    End With
End With
 
Upvote 0
Hey man, thanks a lot. I have to be honest with you, being a novice I do not understand your code. =x

Would you be kind enough to help me speed up this one as well:

Code: 
Sub DeleteAll()
Dim i As Long, lastrow As Long
lastrow = Range("A65536").End(xlUp).Row
 
Sheets("HeldSec").Select
 
For i = lastrow To 2 Step -1
    If Cells(i, 5) <> "X" And Cells(i, 5) <> "W" And Cells(i, 5) <> "Q" And Cells(i, 5) <> "" And Cells(i,8) <> Date - 1 Then
    Cells(i, 1).EntireRow.Delete
    End If
 
End Sub
 
Upvote 0
If you remove the .ClearContents line, you can see the formula that was put in the column to the right of all the data.
Note that the routine deletes the rows that do not return an #DIV/0 error.
Changing the function will change the rows selected by that method.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Speedup Processing
Replies
7
Views
120
vcoolio
V
Rob_010101
  • Solved
Change VB code to move as values, instead of formulas
Replies
3
Views
95
saboh12617
S
E
  • Solved
Delete Row if
Replies
6
Views
304
Eric Penfold
E
R
  • Solved
Delete all rows in column that contain 80 or 800
Replies
4
Views
188
robw824
R
H
  • Question Question
VBA Code Slown Down With Every Run
Replies
7
Views
283
Alex Blakenburg
A

Share this page

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
Back
Top