Is possible to limit a row selection?

Question

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

VBasic2008 · Accepted Answer · 2020-06-11 04:10:37Z

1

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

edited Jun 11, 2020 at 4:10

answered Jun 11, 2020 at 3:58

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Over a year ago

MsgBox "User cancelled." after On Error GoTo local_err seems odd? Maybe they should be reversed?

Gary's Student · Accepted Answer · 2020-06-11 00:10:01Z

0

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.

answered Jun 11, 2020 at 0:10

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

LionRawr Over a year ago

Is still letting me select more than E to I, If the user select (For example) "D to I" then the program will say "Hey dude you can't delete this, is exceding the range" but Thanks Gary, I can apply part of the code for make it better =D

Collectives™ on Stack Overflow

Is possible to limit a row selection?

2 Answers 2

Limited Row Selection

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Limited Row Selection

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related