Urgent Help...

K

kiran

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

TIA
 
L

Lars-Åke Aspelin

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

TIA
Click to expand...

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
 
T

Tausif

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,
 
Top