Hi all,

I looking for a macro that deletes a range when it find a text string in a
specific column. The macro must delete 2 rows after and 3 rows before the
founded range including it (6 entire rows ). the code I'm using only deletes
the row containing the founded text ("xxx"):

Sub deleterows()
Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Columns(1)

Set rngFound = rngToSearch.Find("xxx")
If rngFound Is Nothing Then
MsgBox "No Deletions Found"
Else
Do
rngFound.EntireRow.Delete

Set rngFound = rngToSearch.FindNext
Loop Until rngFound Is Nothing
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Thanks in advance for your help.
Ashmork

try
rngFound.Offset(-3).Resize(6).EntireRow.Delete

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"AshMorK" <(E-Mail Removed)> wrote in message
news:F9D5E8CC-ED2A-4FA5-B137-(E-Mail Removed)...
> Hi all,
>
> I looking for a macro that deletes a range when it find a text string in a
> specific column. The macro must delete 2 rows after and 3 rows before the
> founded range including it (6 entire rows ). the code I'm using only
> deletes
> the row containing the founded text ("xxx"):
>
> Sub deleterows()
> Dim rngToSearch As Range
> Dim wks As Worksheet
> Dim rngFound As Range
>
> Set wks = ActiveSheet
> Set rngToSearch = wks.Columns(1)
>
> Set rngFound = rngToSearch.Find("xxx")
> If rngFound Is Nothing Then
> MsgBox "No Deletions Found"
> Else
> Do
> rngFound.EntireRow.Delete
>
> Set rngFound = rngToSearch.FindNext
> Loop Until rngFound Is Nothing
> End If
> Application.ScreenUpdating = True
> Application.Calculation = xlCalculationAutomatic
> End Sub
>
> Thanks in advance for your help.
> Ashmork

Thanks Don! it works perfectly.

"Don Guillett" wrote:

> try
> rngFound.Offset(-3).Resize(6).EntireRow.Delete
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "AshMorK" <(E-Mail Removed)> wrote in message
> news:F9D5E8CC-ED2A-4FA5-B137-(E-Mail Removed)...
> > Hi all,
> >
> > I looking for a macro that deletes a range when it find a text string in a
> > specific column. The macro must delete 2 rows after and 3 rows before the
> > founded range including it (6 entire rows ). the code I'm using only
> > deletes
> > the row containing the founded text ("xxx"):
> >
> > Sub deleterows()
> > Dim rngToSearch As Range
> > Dim wks As Worksheet
> > Dim rngFound As Range
> >
> > Set wks = ActiveSheet
> > Set rngToSearch = wks.Columns(1)
> >
> > Set rngFound = rngToSearch.Find("xxx")
> > If rngFound Is Nothing Then
> > MsgBox "No Deletions Found"
> > Else
> > Do
> > rngFound.EntireRow.Delete
> >
> > Set rngFound = rngToSearch.FindNext
> > Loop Until rngFound Is Nothing
> > End If
> > Application.ScreenUpdating = True
> > Application.Calculation = xlCalculationAutomatic
> > End Sub
> >
> > Thanks in advance for your help.
> > Ashmork
>
>

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"AshMorK" <(E-Mail Removed)> wrote in message
news:A4ABB629-00AC-48F5-96AB-(E-Mail Removed)...
> Thanks Don! it works perfectly.
>
> "Don Guillett" wrote:
>
>> try
>> rngFound.Offset(-3).Resize(6).EntireRow.Delete
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "AshMorK" <(E-Mail Removed)> wrote in message
>> news:F9D5E8CC-ED2A-4FA5-B137-(E-Mail Removed)...
>> > Hi all,
>> >
>> > I looking for a macro that deletes a range when it find a text string
>> > in a
>> > specific column. The macro must delete 2 rows after and 3 rows before
>> > the
>> > founded range including it (6 entire rows ). the code I'm using only
>> > deletes
>> > the row containing the founded text ("xxx"):
>> >
>> > Sub deleterows()
>> > Dim rngToSearch As Range
>> > Dim wks As Worksheet
>> > Dim rngFound As Range
>> >
>> > Set wks = ActiveSheet
>> > Set rngToSearch = wks.Columns(1)
>> >
>> > Set rngFound = rngToSearch.Find("xxx")
>> > If rngFound Is Nothing Then
>> > MsgBox "No Deletions Found"
>> > Else
>> > Do
>> > rngFound.EntireRow.Delete
>> >
>> > Set rngFound = rngToSearch.FindNext
>> > Loop Until rngFound Is Nothing
>> > End If
>> > Application.ScreenUpdating = True
>> > Application.Calculation = xlCalculationAutomatic
>> > End Sub
>> >
>> > Thanks in advance for your help.
>> > Ashmork
>>
>>