Deleting Rows Using Find

debugcn Published at Dev

blaufer

I am trying to delete rows from the word Modifications to the row before Results. I was trying to use the Find command but I'm not having any luck. Here's one of the many variations I've tried:

Sub FindandDelete()
    Modifications = Range("A1:A1200").Find("Modifications", LookIn:=xlValues)
    Res = Cells.Find("Results", LookIn:=xlValues)
    Range("Modifications:Results").Delete
End Sub

Any ideas or suggestions?

whytheq

Try this:

Sub FindandDelete()

Dim Modifications As Excel.Range
Dim Res As Excel.Range

Set Modifications = Range("A1:A1200").Find(What:="Modifications", LookIn:=xlValues)
Set Res = Range("A1:A1200").Find(What:="Results", LookIn:=xlValues)

If Not (Modifications Is Nothing) And Not (Res Is Nothing) Then
    ActiveSheet.Range(Modifications, Res).EntireRow.Delete 'ClearContents '<<edit to delete rows rather than just clear their contents
End If

End Sub

This is slightly different using row numbers:

Sub FindandDeleteRows()

Dim Modifications As Integer
Dim Res As Integer

Dim lookinRange As Range
Set lookinRange = Excel.ThisWorkbook.ActiveSheet.Range("A1:A1200")

If Not (lookinRange.Find(What:="Modifications", LookIn:=xlValues) Is Nothing) Then
    Modifications = lookinRange.Find(What:="Modifications", LookIn:=xlValues).Row
End If
If Not (lookinRange.Find(What:="Results", LookIn:=xlValues) Is Nothing) Then
    Res = lookinRange.Find(What:="Results", LookIn:=xlValues).Row
End If

ActiveSheet.Rows(Modifications & ":" & Res).ClearContents

End Sub

Collected from the Internet

Please contact [email protected] to delete if infringement.