Is possible to limit a row selection?

1
votes

I'm trying to make a button to delete just one row from a table on Excel using vba but when I press the button and select more than just one row or a lot of cells then deletes everything, I want to limit the selection from E to I (and no matters the number of row) Here is my code and thanks a lot, hope you can help me, I don't find anything similar yet.

Sub delete_a_row()
Dim variable As Range


 On Error GoTo local_err
     CONFIRM = MsgBox("do you wanna delete a row?", vbYesNo + vbExclamation, "delete row")
        If (CONFIRM = vbYes) Then

                Set variable = Application.InputBox("select a row", "delete row", , , , , , 8)
                Range(variable.Address).Select
                Selection.Delete Shift:=xlUp

        End If
local_err:
End Sub

I know looks kinda messy, sorry for that I'm new on VBA

2
excelvba

2 Answers

1
votes

Limited Row Selection

  • The following will delete the first row of the first range (if non-contiguous) of the selection from columns E to I.
  • What I mean by first range is if you select a range starting with row 6 and press CTRL and select another range starting with row 2, then row 6 will be deleted (See Range.Areas).

The Code

Option Explicit

Sub delete_a_row()
    Dim Confirm
    On Error GoTo local_err
    Confirm = MsgBox("do you wanna delete a row?", _
                     vbYesNo + vbExclamation, "delete row")
    If (Confirm = vbYes) Then
        Dim variable As Range
        On Error Resume Next
        Set variable = Application.InputBox("select a row", _
                                            "delete row", , , , , , 8)
        If Err.Number = 0 Then
            On Error GoTo local_err
            With variable
                Range(Cells(.Row, "E"), Cells(.Row, "I")).Delete Shift:=xlUp
            End With
        Else
            On Error GoTo local_err
            MsgBox "User cancelled."
        End If
    Else
        MsgBox "User selected No."
    End If
local_err:
End Sub
0
votes

If the only thing you want deleted is columns E through I then:

Sub ytrewq()
    Dim variable As Range, rw As Long

    Set variable = Application.InputBox("select a row", "delete row", , , , , , 8)
    rw = variable(1).Row

    Range(Cells(rw, "E"), Cells(rw, "I")).Delete Shift:=xlUp
End Sub

This code will remove cells from only a single row.