find and delete rows below

[vikram]

Hi All,


Need ur help!

I need a macro which finds a text in column A and deletes 5 rows belo
it but doesnt delete that row which contains text

text is "Accounting Flexfi"

please help thank

 

[Don Guillett]

try this
Sub finddelete()'ONE line
Cells(Columns(1).Find("whattofind").Row + 1, 1).Resize(5, 1).Delete
End Sub

 

[Don Guillett]

What I sent erases the data in the rows. If you want to DELETE the row, just
add .entirerow
Cells(Columns(1).Find("whattofind").Row + 1, 1).Resize(5,
1).entirerow.Delete
--
Don Guillett
SalesAid Software
(e-mail address removed)

try this
Sub finddelete()'ONE line
Cells(Columns(1).Find("whattofind").Row + 1, 1).Resize(5, 1).Delete
End Sub

 

[vikram]

Hi Don,

This macro is doing nothing. When i run it it doesnt do anything
nothing changes
please hel

 

[vikram]

hey don,

i have this text in my file in column a lot of times , so we need
loop function which finds and deletes in this entire sheet
ur macro deletes just onc

 

[Don Guillett]

I don't have time now but look in vbe HELP for FIND or FINDNEXT. There is a
clear example.

 

[Dave Peterson]

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim FirstAddress As String
Dim FindWhat As String
Dim delRng As Range

FindWhat = "Accounting Flexfi"

With Worksheets("sheet1").Range("a:a")
Set FoundCell = .Cells.Find(what:=FindWhat, _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False)

If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
If delRng Is Nothing Then
Set delRng = FoundCell.Offset(1, 0).Resize(5, 1)
Else
Set delRng = Union(delRng, _
FoundCell.Offset(1, 0).Resize(5, 1))
End If
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireRow.Select
End If
End Sub