SOLVED

deleting entire row using VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-3115238%22%20slang%3D%22en-US%22%3Edeleting%20entire%20row%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3115238%22%20slang%3D%22en-US%22%3E%3CP%3EHI%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20make%20a%20form%20for%20data%20entry%20and%20I%20need%20to%20write%20a%20code%20for%20the%20delete%20button%20on%20the%20form.%20I%20have%20copied%20the%20following%20form%20my%20excel%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20ENTRY_DELETE()%20With%20Sheet1%20If%20MsgBox(%22ARE%20YOU%20SURE%20YOU%20WANT%20TO%20DELETE%20THIS%20ENTRY%3F%22%2C%20vbYesNo%2C%20%22DELETE%20ENTRY%22)%20%3D%20vbNo%20Then%20Exit%20Sub%3C%2FP%3E%3CP%3EIf%20.Range(%22B3%22)%20%3D%20Empty%20Then%20Exit%20Sub%20EntryRow%20%3D%20.Range(%22B3%22).Value%3C%2FP%3E%3CP%3E.Range(EntryRow%20%26amp%3B%20%22.%22%20%26amp%3B%20EntryRow).EntireRow.Delete%20.Range(%22D13%22).Select%3C%2FP%3E%3CP%3EEnd%20With%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3EIm%20very%20new%20to%20this%20so%20any%20help%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3115238%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3116300%22%20slang%3D%22en-US%22%3ERe%3A%20deleting%20entire%20row%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3116300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1296715%22%20target%3D%22_blank%22%3E%40Rory3%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELike%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20ENTRY_DELETE()%0A%20%20%20%20Dim%20EntryRow%20As%20Long%0A%20%20%20%20If%20MsgBox(%22ARE%20YOU%20SURE%20YOU%20WANT%20TO%20DELETE%20THIS%20ENTRY%3F%22%2C%20vbYesNo%2C%20%22DELETE%20ENTRY%22)%20%3D%20vbNo%20Then%20Exit%20Sub%0A%20%20%20%20With%20Sheet1%0A%20%20%20%20%20%20%20%20If%20.Range(%22B3%22)%20%3D%20%22%22%20Then%20Exit%20Sub%0A%20%20%20%20%20%20%20%20EntryRow%20%3D%20.Range(%22B3%22).Value%0A%20%20%20%20%20%20%20%20.Range(%22A%22%20%26amp%3B%20EntryRow).EntireRow.Delete%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121799%22%20slang%3D%22en-US%22%3ERe%3A%20deleting%20entire%20row%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121799%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%2C%3CBR%20%2F%3E%3CBR%20%2F%3EYes%2C%20That%20worked%20perfectly%20Thank%20you%20very%20much.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

HI all.

 

I'm trying to make a form for data entry and I need to write a code for the delete button on the form. I have copied the following form my excel sheet.

 

Sub ENTRY_DELETE() With Sheet1 If MsgBox("ARE YOU SURE YOU WANT TO DELETE THIS ENTRY?", vbYesNo, "DELETE ENTRY") = vbNo Then Exit Sub

If .Range("B3") = Empty Then Exit Sub EntryRow = .Range("B3").Value

.Range(EntryRow & "." & EntryRow).EntireRow.Delete .Range("D13").Select

End With

End Sub

Im very new to this so any help would be appreciated.

 

Thanks.

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@Rory3 

Like this:

Sub ENTRY_DELETE()
    Dim EntryRow As Long
    If MsgBox("ARE YOU SURE YOU WANT TO DELETE THIS ENTRY?", vbYesNo, "DELETE ENTRY") = vbNo Then Exit Sub
    With Sheet1
        If .Range("B3") = "" Then Exit Sub
        EntryRow = .Range("B3").Value
        .Range("A" & EntryRow).EntireRow.Delete
    End With
End Sub

Hi @Hans Vogelaar ,

Yes, That worked perfectly Thank you very much.