Slow Structure - Row Deletion
 

I've got this code to automate the selection and deletion of any rows that
contain a 0 (zero) in column AA. The code works fine, but rather slowly.
Takes almost 1 minute to delete the zero rows in 1 worksheet, and I want to
apply this code to ALOT of worksheets.

Is there anything that can be done to speed this up?

Dim FirstRow, LastRow, CurRow As Long
Dim col As String

FirstRow = 5
col = "AA"

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
Application.ScreenUpdating = False
For CurRow = LastRow To FirstRow Step -1
If ActiveSheet.Cells(CurRow, col) = 0 Then Rows(CurRow).Delete
Next CurRow

MsgBox "Rows deleted!", vbInformation, "Status"

Slow Structure - Row Deletion
 

See this page for a few ways to do it Kirk
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kirk P." wrote in message ...
I've got this code to automate the selection and deletion of any rows that
contain a 0 (zero) in column AA. The code works fine, but rather slowly.
Takes almost 1 minute to delete the zero rows in 1 worksheet, and I want to
apply this code to ALOT of worksheets.

Is there anything that can be done to speed this up?

Dim FirstRow, LastRow, CurRow As Long
Dim col As String

FirstRow = 5
col = "AA"

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
Application.ScreenUpdating = False
For CurRow = LastRow To FirstRow Step -1
If ActiveSheet.Cells(CurRow, col) = 0 Then Rows(CurRow).Delete
Next CurRow

MsgBox "Rows deleted!", vbInformation, "Status"

Slow Structure - Row Deletion
 

Ron, we have a winner. The "auto filter" method is by far the fastest.

Thanks!

"Ron de Bruin" wrote:

See this page for a few ways to do it Kirk
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kirk P." wrote in message ...
I've got this code to automate the selection and deletion of any rows that
contain a 0 (zero) in column AA. The code works fine, but rather slowly.
Takes almost 1 minute to delete the zero rows in 1 worksheet, and I want to
apply this code to ALOT of worksheets.

Is there anything that can be done to speed this up?

Dim FirstRow, LastRow, CurRow As Long
Dim col As String

FirstRow = 5
col = "AA"

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
Application.ScreenUpdating = False
For CurRow = LastRow To FirstRow Step -1
If ActiveSheet.Cells(CurRow, col) = 0 Then Rows(CurRow).Delete
Next CurRow

MsgBox "Rows deleted!", vbInformation, "Status"

Slow Structure - Row Deletion
 

I was hoping to apply this logic to ALL worksheets in a workbook via a loop.
I thought a For-Each statement would do it (code below), but I'm getting an
Object Required error when running the macro. What am I missing?

Sub Delete_with_Autofilter2()
Dim DeleteValue As String
Dim rng As Range
Dim w As Worksheet

DeleteValue = 0
' This will delete the rows with 0 (zero) in the Range("AA5:AA15000")

For Each w In Worksheets
With ActiveSheet
.Range("AA3:AA20000").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next w
End Sub


"Ron de Bruin" wrote:

See this page for a few ways to do it Kirk
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kirk P." wrote in message ...
I've got this code to automate the selection and deletion of any rows that
contain a 0 (zero) in column AA. The code works fine, but rather slowly.
Takes almost 1 minute to delete the zero rows in 1 worksheet, and I want to
apply this code to ALOT of worksheets.

Is there anything that can be done to speed this up?

Dim FirstRow, LastRow, CurRow As Long
Dim col As String

FirstRow = 5
col = "AA"

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
Application.ScreenUpdating = False
For CurRow = LastRow To FirstRow Step -1
If ActiveSheet.Cells(CurRow, col) = 0 Then Rows(CurRow).Delete
Next CurRow

MsgBox "Rows deleted!", vbInformation, "Status"

Slow Structure - Row Deletion
 

Hi Kirk

This is wrong because you not change the sheet in the loop so you always point to the activesheet

Use this

Sub Delete_with_Autofilter2()
Dim DeleteValue As String
Dim rng As Range
Dim w As Worksheet

DeleteValue = 0

For Each w In Worksheets
With w
.Range("AA3:AA20000").AutoFilter Field:=1, Criteria1:=DeleteValue
With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next w
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kirk P." wrote in message ...
I was hoping to apply this logic to ALL worksheets in a workbook via a loop.
I thought a For-Each statement would do it (code below), but I'm getting an
Object Required error when running the macro. What am I missing?

Sub Delete_with_Autofilter2()
Dim DeleteValue As String
Dim rng As Range
Dim w As Worksheet

DeleteValue = 0
' This will delete the rows with 0 (zero) in the Range("AA5:AA15000")

For Each w In Worksheets
With ActiveSheet
.Range("AA3:AA20000").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next w
End Sub


"Ron de Bruin" wrote:

See this page for a few ways to do it Kirk
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kirk P." wrote in message ...
I've got this code to automate the selection and deletion of any rows that
contain a 0 (zero) in column AA. The code works fine, but rather slowly.
Takes almost 1 minute to delete the zero rows in 1 worksheet, and I want to
apply this code to ALOT of worksheets.

Is there anything that can be done to speed this up?

Dim FirstRow, LastRow, CurRow As Long
Dim col As String

FirstRow = 5
col = "AA"

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
Application.ScreenUpdating = False
For CurRow = LastRow To FirstRow Step -1
If ActiveSheet.Cells(CurRow, col) = 0 Then Rows(CurRow).Delete
Next CurRow

MsgBox "Rows deleted!", vbInformation, "Status"

Slow Structure - Row Deletion
 

That did it - thanks!

One final issue - it appears number formatting matters. There is one
worksheet that displays zeros in column AA as 0.00. All others are displayed
as 0. The code doesn't filter out and delete the 0.00, but it DOES work on
the 0.

How can I get it to work for BOTH 0.00 and 0 (i.e. zeros regardless of
formatting)?

"Ron de Bruin" wrote:

Hi Kirk

This is wrong because you not change the sheet in the loop so you always point to the activesheet

Use this

Sub Delete_with_Autofilter2()
Dim DeleteValue As String
Dim rng As Range
Dim w As Worksheet

DeleteValue = 0

For Each w In Worksheets
With w
.Range("AA3:AA20000").AutoFilter Field:=1, Criteria1:=DeleteValue
With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next w
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kirk P." wrote in message ...
I was hoping to apply this logic to ALL worksheets in a workbook via a loop.
I thought a For-Each statement would do it (code below), but I'm getting an
Object Required error when running the macro. What am I missing?

Sub Delete_with_Autofilter2()
Dim DeleteValue As String
Dim rng As Range
Dim w As Worksheet

DeleteValue = 0
' This will delete the rows with 0 (zero) in the Range("AA5:AA15000")

For Each w In Worksheets
With ActiveSheet
.Range("AA3:AA20000").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next w
End Sub


"Ron de Bruin" wrote:

See this page for a few ways to do it Kirk
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kirk P." wrote in message ...
I've got this code to automate the selection and deletion of any rows that
contain a 0 (zero) in column AA. The code works fine, but rather slowly.
Takes almost 1 minute to delete the zero rows in 1 worksheet, and I want to
apply this code to ALOT of worksheets.

Is there anything that can be done to speed this up?

Dim FirstRow, LastRow, CurRow As Long
Dim col As String

FirstRow = 5
col = "AA"

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
Application.ScreenUpdating = False
For CurRow = LastRow To FirstRow Step -1
If ActiveSheet.Cells(CurRow, col) = 0 Then Rows(CurRow).Delete
Next CurRow

MsgBox "Rows deleted!", vbInformation, "Status"

Slow Structure - Row Deletion
 

You can use the two criteria example on the site Kirk

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kirk P." wrote in message ...
That did it - thanks!

One final issue - it appears number formatting matters. There is one
worksheet that displays zeros in column AA as 0.00. All others are displayed
as 0. The code doesn't filter out and delete the 0.00, but it DOES work on
the 0.

How can I get it to work for BOTH 0.00 and 0 (i.e. zeros regardless of
formatting)?

"Ron de Bruin" wrote:

Hi Kirk

This is wrong because you not change the sheet in the loop so you always point to the activesheet

Use this

Sub Delete_with_Autofilter2()
Dim DeleteValue As String
Dim rng As Range
Dim w As Worksheet

DeleteValue = 0

For Each w In Worksheets
With w
.Range("AA3:AA20000").AutoFilter Field:=1, Criteria1:=DeleteValue
With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next w
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kirk P." wrote in message ...
I was hoping to apply this logic to ALL worksheets in a workbook via a loop.
I thought a For-Each statement would do it (code below), but I'm getting an
Object Required error when running the macro. What am I missing?

Sub Delete_with_Autofilter2()
Dim DeleteValue As String
Dim rng As Range
Dim w As Worksheet

DeleteValue = 0
' This will delete the rows with 0 (zero) in the Range("AA5:AA15000")

For Each w In Worksheets
With ActiveSheet
.Range("AA3:AA20000").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next w
End Sub


"Ron de Bruin" wrote:

See this page for a few ways to do it Kirk
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kirk P." wrote in message ...
I've got this code to automate the selection and deletion of any rows that
contain a 0 (zero) in column AA. The code works fine, but rather slowly.
Takes almost 1 minute to delete the zero rows in 1 worksheet, and I want to
apply this code to ALOT of worksheets.

Is there anything that can be done to speed this up?

Dim FirstRow, LastRow, CurRow As Long
Dim col As String

FirstRow = 5
col = "AA"

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
Application.ScreenUpdating = False
For CurRow = LastRow To FirstRow Step -1
If ActiveSheet.Cells(CurRow, col) = 0 Then Rows(CurRow).Delete
Next CurRow

MsgBox "Rows deleted!", vbInformation, "Status"