Macro to find a row and delete it and the 9 rows above it.

R

Rusty_nl

I have searched but cant seem to work out how to do this proparly.
I need a marco that look in Column F for -------
and then it needs to delete that row and the 9 rows above it. And kee
doing that untill all ------ cells are gone.

Anyone an idea
 
B

Bernie Deitrick

Rusty,

See the macro below.

HTH,
Bernie
MS Excel MVP

Sub FindValues2()
Dim c As Range
Dim d As Range

Dim myFindString As String
Dim firstAddress As String

myFindString = "-------"
With Range("F:F")

Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c.Offset(-9, 0).Resize(10, 1)
firstAddress = c.Address
Else:
MsgBox "None were found."
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> firstAddress Then
Do
Set d = Union(d, c.Offset(-9, 0).Resize(10, 1))
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

d.EntireRow.Delete

End Sub
 
Top