Find and delete and remove and remove

Posted: 16 Jan 2012

Tags: Find and delete and remove and remove Find and delete remove

Source: static.rbytes.net

Question:

Hi all

How can I search a worksheet for a string and then delete that entire line and the next 3 lines also, for each time the string is?

TIA,

Sal

Answer:

Hello

ALT + F11 to open the VB Editor, right click "ThisWorkbook" and insert the module and paste the code below on the right. With the sheet, it is to work on the visible water run the code.

Replace the word (string), we are looking for in the line marked

Sub Find_All()
Dim FindRange As Range, c as range
Dim FirstAddress As String
Dim myString As String
MyString = "MyWord" ' change to suit
With ActiveSheet.UsedRange
Set c =.Find (MyString, LookIn: = xlValues, LookAt: = xlWhole)
If not c is nothing then
FirstAddress = c.Address
Do
If FindRange is nothing then
Set FindRange = c.EntireRow.Resize (4)
Other
Set FindRange = Union (FindRange, c.EntireRow.Resize (4))
End If
Set c =.FindNext (c)
< boucle="" while="" not="" c="" est="" rien="" et="" c.address="">FirstAddress
End If
End with
If not FindRange Is Nothing Then
FindRange.Delete
End If
End Sub