| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003: VBA: How to Speed Up Row Deletions
Looking for suggestion on how to change the following as required,
to greatly improve speed of operation. Without the following: about 5-6 seconds WITH the following: about 30 seconds. Dim LastRow As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "x" Then Rows(RowNdx).Delete End If Next RowNdx |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003: VBA: How to Speed Up Row Deletions
One thing that could help is...
ActiveSheet.DisplayPageBreaks = False -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "BEEJAY" wrote in message Looking for suggestion on how to change the following as required, to greatly improve speed of operation. Without the following: about 5-6 seconds WITH the following: about 30 seconds. Dim LastRow As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "x" Then Rows(RowNdx).Delete End If Next RowNdx |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003: VBA: How to Speed Up Row Deletions
The fastest way is to use Find as opposed to traversing each cell
individually. Also you want to do all of the deletes at once instead of one row at a time... Something like this... Sub DeleteStuff() Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Set rngToSearch = Sheets("Sheet1").Columns("B") Set rngFound = rngToSearch.Find(What:="x", _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry nothin found" Else strFirstAddress = rngFound.Address Set rngFoundAll = rngFound Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress rngFoundAll.EntireRow.Delete End If End Sub -- HTH... Jim Thomlinson "BEEJAY" wrote: Looking for suggestion on how to change the following as required, to greatly improve speed of operation. Without the following: about 5-6 seconds WITH the following: about 30 seconds. Dim LastRow As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "x" Then Rows(RowNdx).Delete End If Next RowNdx |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003: VBA: How to Speed Up Row Deletions
Turning calculation to manual, hiding the pagebreaks, and changing to normal
view can increase the speed. You may want to do something like: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub BEEJAY wrote: Looking for suggestion on how to change the following as required, to greatly improve speed of operation. Without the following: about 5-6 seconds WITH the following: about 30 seconds. Dim LastRow As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "x" Then Rows(RowNdx).Delete End If Next RowNdx -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003: VBA: How to Speed Up Row Deletions
I have add a few things here that will help
http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "BEEJAY" wrote in message ... Looking for suggestion on how to change the following as required, to greatly improve speed of operation. Without the following: about 5-6 seconds WITH the following: about 30 seconds. Dim LastRow As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "x" Then Rows(RowNdx).Delete End If Next RowNdx |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003: VBA: How to Speed Up Row Deletions
Question for you Ron. I was under the impression that doing a find / find
next with a union was about the fastest way to delete... Your site shows some code for doing the delete via filtering. Which one is faster??? -- HTH... Jim Thomlinson "Ron de Bruin" wrote: I have add a few things here that will help http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "BEEJAY" wrote in message ... Looking for suggestion on how to change the following as required, to greatly improve speed of operation. Without the following: about 5-6 seconds WITH the following: about 30 seconds. Dim LastRow As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "x" Then Rows(RowNdx).Delete End If Next RowNdx |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003: VBA: How to Speed Up Row Deletions
Hi Jim
I never test it with very big files Jim but I think your code is faster ? -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... Question for you Ron. I was under the impression that doing a find / find next with a union was about the fastest way to delete... Your site shows some code for doing the delete via filtering. Which one is faster??? -- HTH... Jim Thomlinson "Ron de Bruin" wrote: I have add a few things here that will help http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "BEEJAY" wrote in message ... Looking for suggestion on how to change the following as required, to greatly improve speed of operation. Without the following: about 5-6 seconds WITH the following: about 30 seconds. Dim LastRow As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "x" Then Rows(RowNdx).Delete End If Next RowNdx |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel 2003: VBA: How to Speed Up Row Deletions
I'll have to test it (in my spare time... I'm freakin busy)... but as a guess
it should be close enough make no practical difference... -- HTH... Jim Thomlinson "Ron de Bruin" wrote: Hi Jim I never test it with very big files Jim but I think your code is faster ? -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... Question for you Ron. I was under the impression that doing a find / find next with a union was about the fastest way to delete... Your site shows some code for doing the delete via filtering. Which one is faster??? -- HTH... Jim Thomlinson "Ron de Bruin" wrote: I have add a few things here that will help http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "BEEJAY" wrote in message ... Looking for suggestion on how to change the following as required, to greatly improve speed of operation. Without the following: about 5-6 seconds WITH the following: about 30 seconds. Dim LastRow As Long Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row For RowNdx = LastRow To 1 Step -1 If Cells(RowNdx, "B").Value = "x" Then Rows(RowNdx).Delete End If Next RowNdx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 speed up printer | Excel Discussion (Misc queries) | |||
| calculation speed of Excel 2003 and Excel 2000 | Excel Discussion (Misc queries) | |||
| Speed VBA Excel 2003 vs 2007 | Excel Programming | |||
| Trendline Properties/Deletions in Excel VBA | Excel Programming | |||
| Speed--Excel 2003 RAND vs. VBA Rnd... | Excel Programming | |||
Hybrid Mode
