Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make loop code more efficient
Hi,
I ahve the following code that loops thru each row and if the criteria is met it deletes the row. However it runs really slowly. Is there a way to acheive the same thing more effeiciently? Thanks Sub removeNTUs() Dim xR As Long Dim xCw As Integer Dim xCA As Integer Dim wStep As String Dim AStatus As String Dim xStop As Long xCw = 19 xCA = 20 xStop = Workbooks("pipeline reporting.xls").Worksheets ("variables").Cells(2, 2).Value + 12 xR = 15 Do Debug.Print (xR) wStep = Workbooks("pipeline reporting.xls").Worksheets ("Pipeline").Cells(xR, xCw).Value AStatus = Workbooks("pipeline reporting.xls").Worksheets ("Pipeline").Cells(xR, xCA).Value If wStep = "Diary - NTU" And AStatus = "Not Taken Up" Then Rows(xR & ":" & xR).Delete Shift:=xlUp xR = xR - 1 xStop = xStop - 1 End If xR = xR + 1 Loop Until xR = xStop MsgBox ("ended") End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make loop code more efficient
Please follow the below address first.
http://groups.google.com/group/micro...riteria&hl=en& Best regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make loop code more efficient
On 11 Jan, 14:02, " wrote:
Please follow the below address first. http://groups.google.com/group/micro...rogramming/sea... Best regards Thanks, they are all just variations of a loop (for loops, while loops etc etc) and all work very slowly. I have 16000 rows to loop thru and would expect it to run quicker than the current 1 row per second. Maybe it's my system performance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make loop code more efficient
"macroapa" wrote in message ... Hi, I ahve the following code that loops thru each row and if the criteria is met it deletes the row. However it runs really slowly. Is there a way to acheive the same thing more effeiciently? Thanks Sub removeNTUs() Dim xR As Long Dim xCw As Integer Dim xCA As Integer Dim wStep As String Dim AStatus As String Dim xStop As Long xCw = 19 xCA = 20 xStop = Workbooks("pipeline reporting.xls").Worksheets ("variables").Cells(2, 2).Value + 12 xR = 15 Do Debug.Print (xR) wStep = Workbooks("pipeline reporting.xls").Worksheets ("Pipeline").Cells(xR, xCw).Value AStatus = Workbooks("pipeline reporting.xls").Worksheets ("Pipeline").Cells(xR, xCA).Value If wStep = "Diary - NTU" And AStatus = "Not Taken Up" Then Rows(xR & ":" & xR).Delete Shift:=xlUp xR = xR - 1 xStop = xStop - 1 End If xR = xR + 1 Loop Until xR = xStop MsgBox ("ended") End Sub Some ideas .... make sure that screen updating is off. Also, it might be faster if you had a formula on the worksheet which tested for your conditions and equated to true/false and use that to delete rows. Also, when trundling with delete I usually work from the bottom up. I don't know if that is faster or not. Finally, a completely different approach might by to use and advance filter (in place) based on criteria. Mike |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make loop code more efficient
I think I interpreted your initial conditions correctly (first data row is
15, last row to delete up to is found on the "variables" sheet in B2). If so, then see if this code is any faster than what you are running now... Sub RemoveRows() Dim X As Long Dim xStop As Long Dim OriginalCalculationMode As Long Dim RowsToDelete As Range Const xR As Long = 15 Const xCw As String = "S" Const xCA As String = "T" On Error GoTo Whoops OriginalCalculationMode = Application.Calculation Application.Calculation = xlCalculationManual Application.ScreenUpdating = False With Workbooks("pipeline reporting.xls") xStop = .Worksheets("variables").Cells(2, 2).Value + 12 With .Worksheets("Pipeline") xStop = .Cells(.Rows.Count, 1).End(xlUp).Row For X = xStop To xR Step -1 If .Cells(X, xCw).Value = "Diary - NTU" And _ .Cells(X, xCA).Value = "Not Taken Up" Then If RowsToDelete Is Nothing Then Set RowsToDelete = .Cells(X, xCw) Else Set RowsToDelete = Union(RowsToDelete, .Cells(X, xCw)) End If If RowsToDelete.Areas.Count 100 Then RowsToDelete.EntireRow.Delete xlShiftUp Set RowsToDelete = Nothing End If End If Next End With End With If Not RowsToDelete Is Nothing Then RowsToDelete.EntireRow.Delete xlShiftUp End If Whoops: Application.Calculation = OriginalCalculationMode Application.ScreenUpdating = True MsgBox "Ended" End Sub -- Rick (MVP - Excel) "macroapa" wrote in message ... Hi, I ahve the following code that loops thru each row and if the criteria is met it deletes the row. However it runs really slowly. Is there a way to acheive the same thing more effeiciently? Thanks Sub removeNTUs() Dim xR As Long Dim xCw As Integer Dim xCA As Integer Dim wStep As String Dim AStatus As String Dim xStop As Long xCw = 19 xCA = 20 xStop = Workbooks("pipeline reporting.xls").Worksheets ("variables").Cells(2, 2).Value + 12 xR = 15 Do Debug.Print (xR) wStep = Workbooks("pipeline reporting.xls").Worksheets ("Pipeline").Cells(xR, xCw).Value AStatus = Workbooks("pipeline reporting.xls").Worksheets ("Pipeline").Cells(xR, xCA).Value If wStep = "Diary - NTU" And AStatus = "Not Taken Up" Then Rows(xR & ":" & xR).Delete Shift:=xlUp xR = xR - 1 xStop = xStop - 1 End If xR = xR + 1 Loop Until xR = xStop MsgBox ("ended") End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Efficient If's loop | Excel Programming | |||
here is my code...it's running a bit slow though..any ideas to make it more efficient? thanks | Excel Programming | |||
How can I make this code more efficient? | Excel Programming | |||
need to make code more efficient (if possible) | Excel Programming | |||
Loop more efficient ? | Excel Programming |