Accelerating If Statement

[patickherrington]

Hello,

I am using the following If Statement to delete zero value rows in a worksheet.

Dim i As Long
Application.ScreenUpdating = False
For i = 1282 To 11 Step -1
If Cells(i, 5).Value = 0 And Cells(i, 11).Value = 0 Then
Rows(i).Delete Shift:=xlUp
End If
Next i
Application.ScreenUpdating = True

There are over 1200 Rows this If Statement is going through and it is currently taking a long time to run.
Does anyone have an idea on how to speed this up or have a different approach I can try?
Thanks,
Patrick

 

[MrKowz]

Try turning off worksheet calculations. Deleting rows is a (relatively) very slow process in excel, regardless of your approach.

Also, how long is "a long time"? Is there any more to your code?

 

[VoG]

Try

Application.Calculation=xlcalculatemanual

 

[patickherrington]

There is more to the code. I take a list of exported data and filter it. Next, the coding opens an Excel Template i created to complete many of the necessary calculations. The macro then organizes the data for final presentation. Here is the code.

Sub VADR_Macro_2011()
' Created By Patrick Herrington
' Created 2/15/2011
' VADR_Macro_2011 Macro

'Format and Capture Account Browse Data
Range("A1").Select
Selection.Columns.AutoFit
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$Y$28").AutoFilter Field:=2, Criteria1:="=VA", _
Operator:=xlOr, Criteria2:="=VAN"
Cells.Select
Selection.Copy

' Add to Template
Workbooks.Open Filename:="G:\DATA\ASMARKET\Reporting\IAS Reporting Main\MACROS\VADR\VADR Template.xltx"
Sheets("VA Data").Select
Cells.Select
ActiveSheet.Paste

' Refresh VA Contract Detail Pivot Table
Sheets("VA Contract Detail").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

'Populate Contracts Out of Surrender
Sheets("VA Data").Select
Columns("F:F").Select
Selection.Copy
Sheets("Contracts Out of Surrender").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets("VA Data").Select
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VA Data").Select
Sheets("Contracts Out of Surrender").Select
Columns("B:B").Select
ActiveSheet.Paste
Range("D7").Select
Sheets("VA Data").Select
Columns("U:U").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Contracts Out of Surrender").Select
Columns("C:C").Select
ActiveSheet.Paste
Sheets("VA Data").Select
Columns("N:N").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Contracts Out of Surrender").Select
Columns("E:E").Select
ActiveSheet.Paste
Sheets("VA Data").Select
Columns("O:O").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Contracts Out of Surrender").Select
Columns("D:D").Select
ActiveSheet.Paste
Sheets("VA Data").Select
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Contracts Out of Surrender").Select
Columns("F:F").Select
ActiveSheet.Paste

' Identify Contracts in Surrender
Sheets("Contracts Out of Surrender").Select

Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$I$10000").AutoFilter Field:=9, Criteria1:="<>"
Cells.Select
Selection.Columns.AutoFit
ActiveWindow.Zoom = 85

' Format Column Headers
Range("A1:F1").Select
Range("F1").Activate
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With

' Delete Zero Value Contracts on VA Analysis
Sheets("VA Analysis").Select

Dim i As Long
Application.ScreenUpdating = False
For i = 1282 To 11 Step -1
If Cells(i, 5).Value = 0 And Cells(i, 11).Value = 0 Then
Rows(i).Delete Shift:=xlUp
End If
Next i
Application.ScreenUpdating = True

' Hide VA Data and VA Product Info
Sheets("VA Data").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("VA Product Info").Select
ActiveWindow.SelectedSheets.Visible = False

' Lock Dates in Workbook
Sheets("VA Analysis").Select
Range("B2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("VA Contract Detail").Select
Range("D2:F2").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Go to First Tab
Sheets("VA Analysis").Select
End Sub

 

[MrKowz]

It's all the select statements that are slowing down the code. Cleaning those up is the first step. Also, put the application.screenupdating lines on the far edges of your code so that it encompasses the entire thing.

Eg

Sub YourProcedure()
'DIM lines
Application.ScreenUpdating = False
'Your Code
Application.ScreenUpdating = True
End Sub

 

[patickherrington]

First off, thanks for taking the time to help with this.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
So I tested the code again and the If Statement is still causing the largest lag. I was thinking of trying to create a code that first selected all the 0 value Rows and after they are all selected to a bulk delete shift up. Here is what I tried.<o></o>
<o></o>
Dim i As Long
For i = 1282 To 11 Step -1
If Cells(i, 5).Value = 0 And Cells(i, 11).Value = 0 Then
Rows(i).Select<o></o>
End If
Next i
Selection.Delete Shift:=xlUp<o></o>
<o></o>
The problem is the code does not maintain the previous Row selection. Is my thinking right that this might speed it up? If so, how can I alter this?<o></o>
<o></o>
Thanks,<o></o>
Patrick<o></o>

 

[sous2817]

Use the autofilter to filter columns 5 and 11 for 0, then delete the visible cells, that would be the quickest way. There are a ton of examples online and by searching this forum...you could also record a macro and clean up that recorded code a bit. It's a bit late for me, but I'll post some code tomorrow morning (if no one beats me to it first).