Excel VBA excel - find and delete

K

khurshedp

Joined
Jul 31, 2014
Messages
1
Reaction score
0
Hi there

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

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
 
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.

Code: 
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.
 
You can try this code to delete 10 lines from the RecordID

Code: 
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
 
Back
Top