VBA Question - Is there a better approach?

[Scott Wagner]

Below is a piece of code I use to delete rows that contain a specific keyword
in a designated column. Right now I specify the range m2:m1500, but rarely
have that many lines in the raw data. I chose the number 1500 to be sure the
code runs on all lines. Right now this is the largest time contributor to
the macro I run on the data, of which this is a small part.

Is there an approach I can follow other than the one shown below that
addresses the speed, and would also not require me to specify the range... so
it acts only the actual number of row of data in the worksheet?

Thanks,

Scott



'----------------------------------------------------
'Delete extra lines
'----------------------------------------------------
myWords = Array("2")

Set wks = ActiveSheet
With wks
With .Range("m2:m1500")
For iCtr = LBound(myWords) To UBound(myWords)
Do
Set FoundCell = .Cells.Find(What:=myWords(iCtr), _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
Next iCtr
End With
End With

 

[Vasant Nanavati]

Instead of:

..Range("M2:M1500")

try using:

Intersect(.UsedRange, .Range("M2:M1500")

 

[Rick Hansen]

Hi Scott, there is several ways to determine bottom row. See the example
below.
Enjoy Rick

dim LastRow as Long
LastRow = Range("M2").End(xlDn).Row
With Range("M2:M" & LastRow
(your code)

or

With Range("M2: M" & Range("M2").End(xlDn).Row )
(your code)

 

[Martin Krastev]

Dear Scott,

Please see the following code:
'----------------------------------------------------
'Delete extra lines
'----------------------------------------------------
Dim myWords As Variant
Dim wks As Worksheet ' Worksheet object
Dim r As Integer ' Row Iterator
Dim w As Variant ' word iterator among myWords
Dim lastRow As Integer ' Variable to store the last row number

myWords = Array("2") ' Assign values to search for

Application.ScreenUpdating = False ' Disables screen redraw, which is
very slow
Set wks = ActiveSheet ' get reference to the active WorkSheet
lastRow = wks.Columns("M:M").Find("*", wks.Range("M1"), , , ,
xlPrevious).Row ' Find the last cell in the range, containing a value
For r = lastRow To 2 Step -1 ' Iterate from the last to the first row -
important to be backwards, because rows change after deletion
If wks.Cells(r, 13) <> "" Then ' Do not check if cell is empty. Not
necessary
For Each w In myWords ' Iterate among all words
If InStr(wks.Cells(r, 13).Value, w) > 0 Then 'Check if w is
contained in the cell
wks.Rows(r).Delete ' delete the row
Exit For ' exit word loop - no need to check other
words, since row is deleted
End If
Next w
End If
Next r

Application.ScreenUpdating = True ' Enables screen redraw

Please keep in mind that the UsedRange is not always correct in Excel. So
you can find the last cell "looking" backwards as shown above.
Moreover, if many rows are to be deleted, I believe that disabling screen
updating might speed up your code considerably


"Scott Wagner" напиÑа:

 

[Jim Thomlinson]

Since you are using a find it will not make a substantial difference. In fact
I would be inclined to use

With .Columns("M")

Then you do not have to worry about how many cells are populated. You are
not iterating through each of the cells with a find...

If you want to get a performance improvement change the code to accumulate
all of the found ranges into one big range using the union operator and then
just do one big delete at the end. Deleting one row at a time is a
substantial drain on your resources.