Delete Rows
You can use this subroutine to find the word in Column A and then delete
that row on down...
Sub FindAndDeleteDown(WordToFind As String)
Dim LastRow As Long
Dim StartRow As Long
With Worksheets("Sheet5")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For StartRow = 1 To LastRow + 1
If StrComp(.Cells(StartRow, "A").Value, _
WordToFind, vbTextCompare) = 0 Then Exit For
Next
If StartRow < LastRow + 1 Then .Rows(StartRow & ":" & LastRow).Delete
End With
End Sub
Just call this subroutine from within your own passing the word you want to
find as its argument. For example...
SubTest()
FindAndDeleteDown "My Word"
End Sub
Rick
"DougW" wrote in message
...
I would like to write code that would find text in column A and then select
all rows to the last row, and finally delete all the selected rows
including
the original row where the text was found.
I am able to find the text, no problem, and the text is unique to the
first
column. I used ActiveCell.End(xlDown) to get to the last row. But, it
doesn't
select all the rows.
I tried finding the text and then using:
strBeginCell = Selection.Addres
as the beginning address, then using the following to get the ending cell
address:
ActiveCell.End(xlDown).Select
strEndCell = Selection.Address
And, finally using the following to select all the rows, at which point I
get a syntax error:
Range(strBegCell:strEndCell).Select
Thank you.
--
DougW
|