How do you make a macro, which deletes a row if a row is selected, and otherwise prompts up an message box? I made a code like this but it always skips the first if statement and goes on to the next one.

Sub DeleteRow()
If ActiveCell.Row = True Then
Selection.EntireRow.Delete
Else: MsgBox "Choose a row first", vbOKOnly, "Delete a row"
End If
End Sub

CodePudding user response：

You may try below sub

Sub DeleteRow()
    If ActiveCell.Row = Selection.Row Then
        Selection.EntireRow.Delete
        Else: MsgBox "Choose a row first", vbOKOnly, "Delete a row"
    End If
End Sub

Basically you can just run below single line to delete selected row.

Selection.EntireRow.Delete

This should also work

ActiveCell.EntireRow.Delete

CodePudding user response：

Any time you want code to run when you do something to a worksheet, consider using the Worksheet subroutines.

The one you are interested in is the Worksheet_SelectionChange. The Worksheet subroutines (such as the Worksheet_SelectionChange are always stored in the code for that Sheet.) Here are a list of events that the Worksheet has.

Let's break your problem down:

1. When the user selects something, identify what they have selected
2. If the user has selected an entire row, do something OR
3. If the user has selected a single cell, do something else OR
4. If the user has selected anything else, ignore

The Worksheet_SelectionChange takes one parameter: Target, which is the selection that the user made.

Below is the code for deleting a row if it is selected. This should be enough for you to move in the right direction.

Again, the big take away: when responding to user input, always consider the built-in Worksheet subroutines.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ' Purpose:
    ' Delete a row if it is selected

    ' Check if a row was selected
    Dim Address As String
    Address = Target.Address
    Address = Replace(Address, "$", "")
    
    ' Rows and columns have the form #:# when selected
    ' Where # is either the column letter OR the row number
    If InStr(Address, ":") Then
        ' Since a : was in the address, an entire row or column was selected
         
        ' Was a column or row selected?
        ' Check if the members of AddressArray are numbers
        Dim AddressArray As Variant
        AddressArray = Split(Address, ":")
    
        If IsNumeric(AddressArray(0)) And IsNumeric(AddressArray(1)) Then
            ' Both members of the array were numbers, so a row was selected
            
            ' Delete the row
            Target.Delete
        
        End If
    
    End If

End Sub