Urgent Help...
 

Hi all,
I want a macro to find specific word in a column and delete next 2 rows,
kindly help

TIA

Urgent Help...
 

On Thu, 7 Aug 2008 23:14:00 -0700, kiran
wrote:

Hi all,
I want a macro to find specific word in a column and delete next 2 rows,
kindly help

TIA

Assuming the specific word will always be found and there is no need
for handling the opposite case, you may test this macro

Sub delete_two_rows_after_word(aRange As Range, aWord As String)
Cells(aRange.Find(What:=aWord).Row+1,1).Resize(2,1 ).EntireRow.Delete
End Sub

Hope this helps / Lars-Åke

Urgent Help...
 

Hi kiran,

You can also try this if you want. Change the 3 Constants below to what &
where you want to search.

Sub DelRows()

Dim firstaddress As String
Dim c As Range

Const sSearchFor As String = "test" ' Change this to the word that want to
search for
Const iColumn As Integer = 2 ' Change this to the column where you
want the search to begin. 1 = Column A, 2 = Column B & so on.
Const lRowsDelete As Long = 2 'Change this to any number of rows that you
want to be deleted.

With ActiveSheet.Columns(iColumn)
Set c = .Find(sSearchFor, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
ActiveSheet.Range(c.Offset(1).Address & ":" &
c.Offset(lRowsDelete).Address).EntireRow.Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With

Set c = Nothing
End Sub

HTH,
--
Tausif Mohammed


"kiran" wrote:

Hi all,
I want a macro to find specific word in a column and delete next 2 rows,
kindly help

TIA