Hi all,

Can someone tell me what code to put in my macro to enable it to delete
any cell on Sheet1 containing "Word1" "Word2" "Word3" and so on?

When I copy data from our website into excel it contains headings that I
have to delete manually before doing my macro. The headings vary from
day to day so I need to be able to have the macro search for and delete
any or all of them.

Thanks

The "and so on" always seems to pose problems with answering a question such as
this.

How many words could there be and where would they generally be located?

If headings, perhaps just clear contents on a particular row or range or delete
that row entirely.


Gord Dibben MS Excel MVP


On Tue, 19 Dec 2006 17:02:51 -0500, nospam
wrote:

Hi all,

Can someone tell me what code to put in my macro to enable it to delete
any cell on Sheet1 containing "Word1" "Word2" "Word3" and so on?

When I copy data from our website into excel it contains headings that I
have to delete manually before doing my macro. The headings vary from
day to day so I need to be able to have the macro search for and delete
any or all of them.

Thanks

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


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

Hi all,

Can someone tell me what code to put in my macro to enable it to delete
any cell on Sheet1 containing "Word1" "Word2" "Word3" and so on?

When I copy data from our website into excel it contains headings that I
have to delete manually before doing my macro. The headings vary from
day to day so I need to be able to have the macro search for and delete
any or all of them.

Thanks

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

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

This doesn't seem to work. I inserted into the beginning of my macro
code, but keep getting errors - invalid inside procedure at Option
Explicit, etc. Also, do I need to indicated Sheet1 in my code somewhere?
That is where I need to remove the unneeded rows?

Thanks for all your help.

Martin Fishlock wrote:
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.