Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop removal or optimization
I have some code I have written in VBA that is using a Do While loop.
The code works fine, however, it is very slow. I am fairly new to Excel programming and have a very limited knowledge of the available functionality in the VBA language. So I was wondering if someone might help me optimize my loop or possibly remove it completely. It seems to me that there should be some command I don't know about that would really help me out. Maybe a sort or find or putting results in a collection or something, I just don't know. My code basically compares the value of the (intField) column to a string (Str) for every row from the first to last (both ints) row. If the value of the (intField) column is not equal to Str then the row is deleted and the remaining rows are shifted up. Here is my code. i = first last = last + 1 Do While i < last If Not Trim(ws.Cells(i, intField).Value) = Str Then ws.Rows(i).Delete Shift:=xlShiftUp ' need to recheck same row b/c of shift and there is one less row i = i - 1 last = last - 1 End If i = i + 1 Loop Thanks for any help in advance. If you need any other info, just ask. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop removal or optimization
Well, one thing you could do that would probably speed it up a bit is
to stop the screen from updating while the macro is running. Use: Application.ScreenUpdating = False before your loop and make sure to turn it back on after your loop using: Application.ScreenUpdating = True This help speed things up for me when deleting rows and such. -JK -matt wrote: I have some code I have written in VBA that is using a Do While loop. The code works fine, however, it is very slow. I am fairly new to Excel programming and have a very limited knowledge of the available functionality in the VBA language. So I was wondering if someone might help me optimize my loop or possibly remove it completely. It seems to me that there should be some command I don't know about that would really help me out. Maybe a sort or find or putting results in a collection or something, I just don't know. My code basically compares the value of the (intField) column to a string (Str) for every row from the first to last (both ints) row. If the value of the (intField) column is not equal to Str then the row is deleted and the remaining rows are shifted up. Here is my code. i = first last = last + 1 Do While i < last If Not Trim(ws.Cells(i, intField).Value) = Str Then ws.Rows(i).Delete Shift:=xlShiftUp ' need to recheck same row b/c of shift and there is one less row i = i - 1 last = last - 1 End If i = i + 1 Loop Thanks for any help in advance. If you need any other info, just ask. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop removal or optimization
I agree that turning off ScreenUpdating is a good idea.
You might also want to turn of automatic calculation for the duration of the loop (as long as this doesn't prevent the values you're checking from being set correctly). dim oldCalcMode as Long oldCalcMode = Application.Calculation Application.Calculation = xlCalculationManual ' ' your code here ' Application.Calculation = oldCalcMode Andrew JK wrote: Well, one thing you could do that would probably speed it up a bit is to stop the screen from updating while the macro is running. Use: Application.ScreenUpdating = False before your loop and make sure to turn it back on after your loop using: Application.ScreenUpdating = True This help speed things up for me when deleting rows and such. -JK -matt wrote: I have some code I have written in VBA that is using a Do While loop. The code works fine, however, it is very slow. I am fairly new to Excel programming and have a very limited knowledge of the available functionality in the VBA language. So I was wondering if someone might help me optimize my loop or possibly remove it completely. It seems to me that there should be some command I don't know about that would really help me out. Maybe a sort or find or putting results in a collection or something, I just don't know. My code basically compares the value of the (intField) column to a string (Str) for every row from the first to last (both ints) row. If the value of the (intField) column is not equal to Str then the row is deleted and the remaining rows are shifted up. Here is my code. i = first last = last + 1 Do While i < last If Not Trim(ws.Cells(i, intField).Value) = Str Then ws.Rows(i).Delete Shift:=xlShiftUp ' need to recheck same row b/c of shift and there is one less row i = i - 1 last = last - 1 End If i = i + 1 Loop Thanks for any help in advance. If you need any other info, just ask. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop removal or optimization
Hi Matt,
Try something like: '================ Public Sub DeleteRange() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim delRng As Range Dim iLastRow As Long Dim i As Long Dim CalcMode As Long Dim ViewMode As Long Const sStr As String = "ABC" '<<===== CHANGE Set WB = ActiveWorkbook '<<===== CHANGE Set SH = WB.Sheets("Sheet3") '<<===== CHANGE On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveWindow ViewMode = .View .View = xlNormalView End With SH.DisplayPageBreaks = False iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To iLastRow If Not Trim(Cells(i, "A").Value) = sStr Then If delRng Is Nothing Then Set delRng = Cells(i, "A") Else Set delRng = Union(Cells(i, "A"), delRng) End If End If Next i If Not delRng Is Nothing Then delRng.EntireRow.Delete End If XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With ActiveWindow.View = ViewMode End Sub '<<================ --- Regards, Norman "-matt" wrote in message oups.com... I have some code I have written in VBA that is using a Do While loop. The code works fine, however, it is very slow. I am fairly new to Excel programming and have a very limited knowledge of the available functionality in the VBA language. So I was wondering if someone might help me optimize my loop or possibly remove it completely. It seems to me that there should be some command I don't know about that would really help me out. Maybe a sort or find or putting results in a collection or something, I just don't know. My code basically compares the value of the (intField) column to a string (Str) for every row from the first to last (both ints) row. If the value of the (intField) column is not equal to Str then the row is deleted and the remaining rows are shifted up. Here is my code. i = first last = last + 1 Do While i < last If Not Trim(ws.Cells(i, intField).Value) = Str Then ws.Rows(i).Delete Shift:=xlShiftUp ' need to recheck same row b/c of shift and there is one less row i = i - 1 last = last - 1 End If i = i + 1 Loop Thanks for any help in advance. If you need any other info, just ask. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop removal or optimization
Hi Matt,
Replace my suggested code with: '================ Public Sub DeleteRange() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim delRng As Range Dim iLastRow As Long Dim i As Long Dim CalcMode As Long Dim ViewMode As Long Const sStr As String = "ABC" '<<===== CHANGE Set WB = ActiveWorkbook '<<===== CHANGE Set SH = WB.Sheets("Sheet3") '<<===== CHANGE On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveWindow ViewMode = .View .View = xlNormalView End With SH.DisplayPageBreaks = False With SH iLastRow = .Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To iLastRow If Not Trim(.Cells(i, "A").Value) = sStr Then If delRng Is Nothing Then Set delRng = .Cells(i, "A") Else Set delRng = Union(.Cells(i, "A"), delRng) End If End If Next i End With If Not delRng Is Nothing Then delRng.EntireRow.Delete End If XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With ActiveWindow.View = ViewMode End Sub '<<================ -- --- Regards, Norman "Norman Jones" wrote in message ... Hi Matt, Try something like: '================ Public Sub DeleteRange() Dim WB As Workbook Dim SH As Worksheet Dim rng As Range Dim delRng As Range Dim iLastRow As Long Dim i As Long Dim CalcMode As Long Dim ViewMode As Long Const sStr As String = "ABC" '<<===== CHANGE Set WB = ActiveWorkbook '<<===== CHANGE Set SH = WB.Sheets("Sheet3") '<<===== CHANGE On Error GoTo XIT With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveWindow ViewMode = .View .View = xlNormalView End With SH.DisplayPageBreaks = False iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To iLastRow If Not Trim(Cells(i, "A").Value) = sStr Then If delRng Is Nothing Then Set delRng = Cells(i, "A") Else Set delRng = Union(Cells(i, "A"), delRng) End If End If Next i If Not delRng Is Nothing Then delRng.EntireRow.Delete End If XIT: With Application .Calculation = CalcMode .ScreenUpdating = True End With ActiveWindow.View = ViewMode End Sub '<<================ --- Regards, Norman "-matt" wrote in message oups.com... I have some code I have written in VBA that is using a Do While loop. The code works fine, however, it is very slow. I am fairly new to Excel programming and have a very limited knowledge of the available functionality in the VBA language. So I was wondering if someone might help me optimize my loop or possibly remove it completely. It seems to me that there should be some command I don't know about that would really help me out. Maybe a sort or find or putting results in a collection or something, I just don't know. My code basically compares the value of the (intField) column to a string (Str) for every row from the first to last (both ints) row. If the value of the (intField) column is not equal to Str then the row is deleted and the remaining rows are shifted up. Here is my code. i = first last = last + 1 Do While i < last If Not Trim(ws.Cells(i, intField).Value) = Str Then ws.Rows(i).Delete Shift:=xlShiftUp ' need to recheck same row b/c of shift and there is one less row i = i - 1 last = last - 1 End If i = i + 1 Loop Thanks for any help in advance. If you need any other info, just ask. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop removal or optimization
Thanks for the help everyone. I will give it a try. Sorry I forgot to
mention that I have already turned off ScreenUpdating, but I haven't heard of that Application.Calculation thing so I will give that a try too. Norman- thanks for the code. I'll let you know how it turns out. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop removal or optimization
Thanks for the help Norman. It worked like a charm. It sorted almost
instantly. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Excel Worksheet Functions | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Excel Discussion (Misc queries) | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Setting up and Configuration of Excel | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Links and Linking in Excel | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Charts and Charting in Excel |