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" <(E-Mail Removed)> wrote in message news:61A96BB5-5EC6-4CF2-A306-(E-Mail Removed)...
> 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
>> >
Similar Threads
