Code for removing rows in excel based on the text

This was quite difficult for me because I don't know visual basic, but it seems to work so I thought I would put it here in case I ever wanted it again or someone else can make use of it.



Sub RemoveRows()

' Macro created 15/11/2007 by Monica

Dim Cell As Object
Dim wordfound As Boolean
Dim ans, startpt As String
Dim N, Count As Integer

'work out where the selection is to start

startpt = InputBox("Enter the cell you wish to start it in the form A1")

ActiveSheet.Range(startpt, _
ActiveSheet.Range(startpt).End(xlDown).End(xlToRight)).Select

'work out how many occurrances there are of the text being looked for
Count = 0
ans = InputBox("Enter the text in the lines you are wanting to delete")

For Each Cell In Selection
N = InStr(1, Cell.Value, ans, vbTextCompare)
While N <> 0
Count = Count + 1
N = InStr(N + 1, Cell.Value, ans)
Wend
Next Cell

MsgBox Count & " Occurrences of " & ans & "count is " & Count

'find the text and delete the row
While Count > 0

'Select a single cell to start each search
ActiveSheet.Cells(1, 1).Select

Cells.Find(What:=ans, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Selection.EntireRow.Delete

Count = Count - 1
Wend

End Sub

Hmmmm Interesting

I wonder if I will ever have anything to say that anyone else would consider to be interesting? Here is a picture from my 2005 Christmas holiday anyway.