VBA to delete rows

[mannm4]

I am trying to write a macro that deletes rows based on certain criteria. I have rows with the word "Summary:" in it that I want to keep (no problem). My issue is that i then have rows that all begin with "Application:". What comes after that is different (i.e. Application: Start, Application: End). I need help creating this wildcard search as I have tried with no succes. This is what I have so far:

Sub A()
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\MANNM4\Desktop\Copy of appt center (dec 2009)-test")
i = 1

Do Until objExcel.Cells(i, 1).Value = "Done"
    If objExcel.Cells(i, 1).Value <> "Summary:" And objExcel.Cells(i, 2).Value <> "Offered" Then
        Set objRange = objExcel.Cells(i, 1).EntireRow
        objRange.Delete
        i = i - 1
    End If
    i = i + 1
Loop

End Sub

[Leith Ross]

Hello mannm4,

You can use the Like operator to perform partial matches on strings.

  'To test if the beginning of the string starts with Application: Comparisons are case sensitive.
   objExcel.Cells(i, 1).Value Like "Application:*"

Returns True if the start of the string matches, otherwise False.

[Richard Buttrey]

Hi,

Wherever possible you should try and avoid looping through cells, particularly with large ranges since there is a time overhead each time the VBE jumps back to the Excel Application (and vice versa) to perform a task, as it does each tine it deletes a row. It's always much better to use standard excel functionality and use that.

So in this case make the first part of the macro a task to create an Excel function that identifies which rows should be deleted and copy that down a helper column adjacent to your data. For instance

=IF(AND(A1)="Summary",Left(B1,6)="Applic"),"Delete","Keep")

Now filter your range using this helper column and select on "Delete", then finally delete all the filtered rows. You can use VBA code like:

Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).EntireRow.Delete

HTH