Excel Forum

Code: Select all

Private Sub DeleteSelected_Click()

    Dim i, lastrow As Long
    
    lastrow = Sheets("edit and delete").Range("B" & Rows.Count).End(xlUp).Row
    
    If MsgBox("Are you sure you want to delete the row?", vbYesNo + vbQuestion, "Delete row") = vbYes Then
        For i = 1 To lastrow
            If Cells(i, 2) = ListBox1.List(ListBox1.ListIndex) Then
                Range("B" & i, "C" & i).Select
                Range("B" & i, "C" & i).ClearContents
                Range("B" & i + 1, "C" & lastrow).Select
                Range("B" & i + 1, "C" & lastrow).Copy Destination: Sheets("edit and delete").Range("B" & i, "C" & i).Value
                Range("B" & i + lastrow, "C" & lastrow).ClearContents
            End If
        Next i
    End If
End Sub

Code: Select all

Range("B" & i + 1, "C" & lastrow).Copy Destination: Sheets("edit and delete").Range("B" & i, "C" & i).Value

Code: Select all

'Other code
For i = 1 To lastrow
    If Cells(i, 2) = ListBox1.List(ListBox1.ListIndex) Then
        Cells(i, 2).Resize(1, 2).delete shift:=xlUp
        Exit For
    End If
Next i
'Other code

Code: Select all

Cells(i, 2).Resize(1, 2).delete shift:=xlUp

Code: Select all

'Other code
Dim temp as Variant
For i = 1 To lastrow
    If Cells(i, 2) = ListBox1.List(ListBox1.ListIndex) Then
        If Cells(i + 1, 2).Value <> "" Then
            temp = Range(Cells(i + 1, 2), Cells(Rows.Count, 2).End(xlUp)).Resize(, 2)
            Range(Cells(i, 2), Cells(Rows.Count, 3).End(xlUp)).ClearContents
            Cells(i, 2).Resize(UBound(temp, 1), 2).Value = temp
        Else
            Range(Cells(i, 2), Cells(Rows.Count, 3).End(xlUp)).ClearContents
        End If
        Exit For
    End If
Next i
'Other code