Prompt Specific Row to Delete

[03856me]

Is it possible to prompt the user for a specific row number to delete in a vba textbox? If yes, then the code would fire using a button. I have the worksheet protected and only want to unprotect through code to delete a specific row number, then protect it again.

 

[jproffer]

Maybe something like this:

Sub test()
Dim MyRow As Long
Sheets("Name").Unprotect "Password"
    MyRow = InputBox("Which Row would you like to delete?")
    Sheets("Name").Range("A" & MyRow).EntireRow.Delete
Sheets("Name").Protect "Password"
End Sub

 

[03856me]

Worked perfectly - thank you so much......this web site rocks!

 

[Colin Legg]

What happens if the user presses cancel? You might want to cater for that and also if the user enters an invalid row reference.

 

[Trebor76]

Good point Colin - 03856me try this:

Sub DeleteMyRow()

    Dim lngMyRowNum As Long
    
    On Error Resume Next
        lngMyRowNum = CLng(InputBox("Enter the desired row number to be deleted i.e. 50"))
    On Error GoTo 0
    
    If lngMyRowNum = 0 Then 'The user has clicked the <Cancel> button or an invalid number (i.e. text / alphanumeric) was entered
        Exit Sub
    Else
        With ActiveSheet
            .Unprotect "YourPasswordHere"
            .Range("A" & lngMyRowNum).EntireRow.Delete
            .Protect "YourPasswordHere"
        End With
    End If

End Sub

HTH

Robert

 

[03856me]

Yes, you guys are right, that was a problem, thanks for the help, jproffer code worked like a charm.