Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Needed To Loop Through Certain Rows
I'm using VBA 6.5 and trying to write the code other users will initiate via
a control within a file/worksheet. I have code that prepares imported data in a worksheet, which essentially identifies rows that should be untouched as well as those that should be deleted. I need to incorporate a portion of code to loop through rows 1 through 15,000 and then again through 20,000 through 25,000, and delete each row which has the word "DELETE" in a given column of the worksheet. I tried the code below, but since it loops through all the rows of the file, it takes quite awhile to complete. Since this is will be excuted by front-end users, the code needs to be added to the existing macro/control. Question: How can I edit the code to only process through the two sets of rows (ie 1-15,000 and then 20,000-25,000)? If this needs to be done in one step to address the first set of rows, and a secondary step for the other set of rows, that is fine. Any assistance would be greatly appreciated. I do not have much experience posting questions, so hopefully this question is being sent to the proper group. Thank You 'DELETES ROWS PREDETERMINED TO BE DELETE-ABLE Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "AY").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "AY").Value = "DELETE" Then .Rows(Lrow).Delete End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Needed To Loop Through Certain Rows
Give this macro a try...
Sub DELETErows() Dim C As Range, FoundCells As Range, FirstAddress As String With ActiveSheet.Columns("AY") Set C = .Find("DELETE", LookIn:=xlValues, _ LookAt:=xlWhole, MatchCase:=False) If Not C Is Nothing Then FirstAddress = C.Address Do If FoundCells Is Nothing Then Set FoundCells = C Else Set FoundCells = Union(FoundCells, C) End If Set C = .Find("DELETE", After:=C, LookIn:=xlValues, _ LookAt:=xlWhole, MatchCase:=False) Loop While Not C Is Nothing And C.Address < FirstAddress End If If Not FoundCells Is Nothing Then FoundCells.EntireRow.Delete End With End Sub -- Rick (MVP - Excel) "MWS-C360" wrote in message ... I'm using VBA 6.5 and trying to write the code other users will initiate via a control within a file/worksheet. I have code that prepares imported data in a worksheet, which essentially identifies rows that should be untouched as well as those that should be deleted. I need to incorporate a portion of code to loop through rows 1 through 15,000 and then again through 20,000 through 25,000, and delete each row which has the word "DELETE" in a given column of the worksheet. I tried the code below, but since it loops through all the rows of the file, it takes quite awhile to complete. Since this is will be excuted by front-end users, the code needs to be added to the existing macro/control. Question: How can I edit the code to only process through the two sets of rows (ie 1-15,000 and then 20,000-25,000)? If this needs to be done in one step to address the first set of rows, and a secondary step for the other set of rows, that is fine. Any assistance would be greatly appreciated. I do not have much experience posting questions, so hopefully this question is being sent to the proper group. Thank You 'DELETES ROWS PREDETERMINED TO BE DELETE-ABLE Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "AY").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "AY").Value = "DELETE" Then .Rows(Lrow).Delete End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Needed To Loop Through Certain Rows
Hi MWS-C360
See the filter or union or example http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "MWS-C360" wrote in message ... I'm using VBA 6.5 and trying to write the code other users will initiate via a control within a file/worksheet. I have code that prepares imported data in a worksheet, which essentially identifies rows that should be untouched as well as those that should be deleted. I need to incorporate a portion of code to loop through rows 1 through 15,000 and then again through 20,000 through 25,000, and delete each row which has the word "DELETE" in a given column of the worksheet. I tried the code below, but since it loops through all the rows of the file, it takes quite awhile to complete. Since this is will be excuted by front-end users, the code needs to be added to the existing macro/control. Question: How can I edit the code to only process through the two sets of rows (ie 1-15,000 and then 20,000-25,000)? If this needs to be done in one step to address the first set of rows, and a secondary step for the other set of rows, that is fine. Any assistance would be greatly appreciated. I do not have much experience posting questions, so hopefully this question is being sent to the proper group. Thank You 'DELETES ROWS PREDETERMINED TO BE DELETE-ABLE Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "AY").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "AY").Value = "DELETE" Then .Rows(Lrow).Delete End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Needed To Loop Through Certain Rows
I would just run two different loops. Plus I modified the code just a bit.
There really isn't a need to use ActiveSheet in this case because in code like this below it is assumed it is the activesheet. Hope this helps! If so, click "YES" below. Sub DeleteRows() Dim i As Long Dim FirstRow As Long Dim LastRow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ' delete rows with "DELETE" 1-15000 FirstRow = 1 LastRow = 15000 For i = LastRow To FirstRow Step -1 If Cells(i, "AY").Value = "DELETE" Then Rows(i).EntireRow.Delete Shift:=xlUp End If Next i ' delete rows with "DELETE" 20000-25000 FirstRow = 20000 LastRow = 25000 For i = LastRow To FirstRow Step -1 If Cells(i, "AY").Value = "DELETE" Then Rows(i).EntireRow.Delete Shift:=xlUp End If Next i ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Cheers, Ryan "MWS-C360" wrote: I'm using VBA 6.5 and trying to write the code other users will initiate via a control within a file/worksheet. I have code that prepares imported data in a worksheet, which essentially identifies rows that should be untouched as well as those that should be deleted. I need to incorporate a portion of code to loop through rows 1 through 15,000 and then again through 20,000 through 25,000, and delete each row which has the word "DELETE" in a given column of the worksheet. I tried the code below, but since it loops through all the rows of the file, it takes quite awhile to complete. Since this is will be excuted by front-end users, the code needs to be added to the existing macro/control. Question: How can I edit the code to only process through the two sets of rows (ie 1-15,000 and then 20,000-25,000)? If this needs to be done in one step to address the first set of rows, and a secondary step for the other set of rows, that is fine. Any assistance would be greatly appreciated. I do not have much experience posting questions, so hopefully this question is being sent to the proper group. Thank You 'DELETES ROWS PREDETERMINED TO BE DELETE-ABLE Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "AY").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "AY").Value = "DELETE" Then .Rows(Lrow).Delete End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Needed To Loop Through Certain Rows
Ryan, Thank You for taking the time to try and help me, I appreciate it!!
This worked perfectly -- Thank You!!!! "Ryan H" wrote: I would just run two different loops. Plus I modified the code just a bit. There really isn't a need to use ActiveSheet in this case because in code like this below it is assumed it is the activesheet. Hope this helps! If so, click "YES" below. Sub DeleteRows() Dim i As Long Dim FirstRow As Long Dim LastRow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ' delete rows with "DELETE" 1-15000 FirstRow = 1 LastRow = 15000 For i = LastRow To FirstRow Step -1 If Cells(i, "AY").Value = "DELETE" Then Rows(i).EntireRow.Delete Shift:=xlUp End If Next i ' delete rows with "DELETE" 20000-25000 FirstRow = 20000 LastRow = 25000 For i = LastRow To FirstRow Step -1 If Cells(i, "AY").Value = "DELETE" Then Rows(i).EntireRow.Delete Shift:=xlUp End If Next i ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Cheers, Ryan "MWS-C360" wrote: I'm using VBA 6.5 and trying to write the code other users will initiate via a control within a file/worksheet. I have code that prepares imported data in a worksheet, which essentially identifies rows that should be untouched as well as those that should be deleted. I need to incorporate a portion of code to loop through rows 1 through 15,000 and then again through 20,000 through 25,000, and delete each row which has the word "DELETE" in a given column of the worksheet. I tried the code below, but since it loops through all the rows of the file, it takes quite awhile to complete. Since this is will be excuted by front-end users, the code needs to be added to the existing macro/control. Question: How can I edit the code to only process through the two sets of rows (ie 1-15,000 and then 20,000-25,000)? If this needs to be done in one step to address the first set of rows, and a secondary step for the other set of rows, that is fine. Any assistance would be greatly appreciated. I do not have much experience posting questions, so hopefully this question is being sent to the proper group. Thank You 'DELETES ROWS PREDETERMINED TO BE DELETE-ABLE Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "AY").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "AY").Value = "DELETE" Then .Rows(Lrow).Delete End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Needed To Loop Through Certain Rows
Filter or union is much faster with a lot of data
See my site for examples or see Ricks example -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "MWS-C360" wrote in message ... Ryan, Thank You for taking the time to try and help me, I appreciate it!! This worked perfectly -- Thank You!!!! "Ryan H" wrote: I would just run two different loops. Plus I modified the code just a bit. There really isn't a need to use ActiveSheet in this case because in code like this below it is assumed it is the activesheet. Hope this helps! If so, click "YES" below. Sub DeleteRows() Dim i As Long Dim FirstRow As Long Dim LastRow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ' delete rows with "DELETE" 1-15000 FirstRow = 1 LastRow = 15000 For i = LastRow To FirstRow Step -1 If Cells(i, "AY").Value = "DELETE" Then Rows(i).EntireRow.Delete Shift:=xlUp End If Next i ' delete rows with "DELETE" 20000-25000 FirstRow = 20000 LastRow = 25000 For i = LastRow To FirstRow Step -1 If Cells(i, "AY").Value = "DELETE" Then Rows(i).EntireRow.Delete Shift:=xlUp End If Next i ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Cheers, Ryan "MWS-C360" wrote: I'm using VBA 6.5 and trying to write the code other users will initiate via a control within a file/worksheet. I have code that prepares imported data in a worksheet, which essentially identifies rows that should be untouched as well as those that should be deleted. I need to incorporate a portion of code to loop through rows 1 through 15,000 and then again through 20,000 through 25,000, and delete each row which has the word "DELETE" in a given column of the worksheet. I tried the code below, but since it loops through all the rows of the file, it takes quite awhile to complete. Since this is will be excuted by front-end users, the code needs to be added to the existing macro/control. Question: How can I edit the code to only process through the two sets of rows (ie 1-15,000 and then 20,000-25,000)? If this needs to be done in one step to address the first set of rows, and a secondary step for the other set of rows, that is fine. Any assistance would be greatly appreciated. I do not have much experience posting questions, so hopefully this question is being sent to the proper group. Thank You 'DELETES ROWS PREDETERMINED TO BE DELETE-ABLE Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "AY").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "AY").Value = "DELETE" Then .Rows(Lrow).Delete End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple loop needed please | Excel Discussion (Misc queries) | |||
VB Script Code needed for Grouping Rows | Excel Discussion (Misc queries) | |||
Code needed to copy rows. | Excel Programming | |||
Help needed on loop | Excel Programming | |||
loop code needed | Excel Programming |