Karl,

Change
szWords = Array("word1", "word2", "word3")
to
szWords = Array("General Boarding", "Wait List")
and it will delete the all rows on the active sheet that contain one or all
of the headings in quotes above.

You can then do the other things.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"nospam" <"karlb65(nospam)" wrote:

Martin,

The rows I need to delete commonly say "General Boarding" and "Wait
List". I need to make this the first step in my macro. I want to insert
something for one sheet only to remove these particular rows so they
will not be copied to another sheet which is part of my macro.

Will what you have below work for this scenario, or is there something
more simplified?

Martin Fishlock wrote:
karl,

Try this:


Option Explicit
Option Compare Text
Sub deleterows()
Dim szWords As Variant
szWords = Array("word1", "word2", "word3")

Dim lWords As Long, lWordUBound As Long, lWordLBound As Long
lWordLBound = LBound(szWords)
lWordUBound = UBound(szWords)

Dim lRow As Long, lCol As Long
Dim lRowStart As Long, lRowEnd As Long
Dim lColStart As Long, lColEnd As Long
Dim rUsed As Range

Set rUsed = ActiveSheet.UsedRange

lRowStart = rUsed.Row
lRowEnd = lRowStart + rUsed.Rows.Count - 1
lColStart = rUsed.Column
lColEnd = lColStart + rUsed.Columns.Count - 1

For lRow = lRowEnd To lRowStart Step -1
For lCol = lColEnd To lColStart Step -1
For lWords = lWordLBound To lWordUBound
If Cells(lRow, lCol) = szWords(lWords) Then
' found so delete it and move on to next row
Rows(lRow).Delete
Exit For
End If
Next lWords
Next lCol
Next lRow

End Sub