Forum Discussion

Jarvo's avatar
Jarvo
Copper Contributor
Mar 17, 2021
Solved

VBA preventing me from deleting or highlighting rows and columns

Hi Everyone,   I am using this formula (shown below) to copy all the data entered in a row from one sheet to another sheet, depending on cue words entered in a particular column. The problem I'm ha...
  • HansVogelaar's avatar
    Mar 17, 2021

    Jarvo 

    If you select more than one cell (for example an entire row, Target.Value is not a single value, but an array of values, so the comparison UCase(Target) = "SAVE" is not valid.

    Add the following line at the beginning of the procedure:

     

    If Target.CountLarge > 1 Then Exit Sub

     

    Since the rest of the code will only be executed if Target is a single cell, you can replace Cells(Target.Row, Target.Column) with Target.

     

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.CountLarge > 1 Then Exit Sub
        Application.EnableEvents = False
        If Target.Column = 4 And UCase(Target) = "SAVE" Then
            Target.EntireRow.Copy Destination:= _
                Sheets("Saved").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
        If Target.Column = 4 And UCase(Target) = "CLOSED" Then
            Target.EntireRow.Copy Destination:= _
                Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
        Application.EnableEvents = True
    End Sub

Resources