Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow Excel Navigation with Up / Down Arrow and slow scrolling | Excel Discussion (Misc queries) | |||
A VB problem with a if structure | Excel Programming | |||
Slow opening directory structure in Excel 2002 | Excel Discussion (Misc queries) | |||
if structure help | Excel Programming | |||
Structure of If...Else in VBA | Excel Programming |