Delete rows macro
 

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

Delete rows macro
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AshMorK" wrote in message
...
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

Delete rows macro
 

Thanks Don! it works perfectly.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AshMorK" wrote in message
...
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

Delete rows macro
 

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AshMorK" wrote in message
...
Thanks Don! it works perfectly.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AshMorK" wrote in message
...
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