Excel VBA excel - find and delete

[khurshedp]

Hi there

I am new to VBA on excel. Can someone help please.

I have a spreadsheet of many columns and rows. I need to find the word "Record Id" (which is normally in column A somewhere) and then delete 10 lines from where the Record ID word is.

Right now I have to do it manually by searching Ctrl + f and then selecting 10 lines and deleting. But if I can have a code, it will be much faster.

Thanks

 

[davesexcel]

This code will pop up an imput box, asking what you want to find, will then find it and delete 10 cells below it as well.

Sub Button1_Click()
    Dim s As String, r As Range
    Dim Rws As Long, Rng As Range
    Rws = Cells(Rows.Count, "A").End(xlUp).Row
    Set Rng = Range(Cells(1, 1), Cells(Rws, 1))
    s = InputBox("What do you want to find?")
    Set r = Rng.Find(what:=s, lookat:=xlWhole)
    Range("A" & r.Row & ":A" & r.Row + 10).Clear

End Sub

You can also use loops to find a criteria.

 

[Monika]

You can try this code to delete 10 lines from the RecordID

Sub QuickCull()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("Sheet1")
Set rng1 = ws.Range(ws.[b2], ws.Cells(Rows.Count, "B").End(xlUp))
Application.ScreenUpdating = False
With ActiveSheet
        .AutoFilterMode = False
        rng1.AutoFilter Field:=1, Criteria1:="Record Id"
        rng1.Offset(1, 0).EntireRow.Delete
        .AutoFilterMode = False
    End With
Application.ScreenUpdating = True
End Sub