Snasui Excel Forum

Code: Select all

Sub Button3_Click()
    Dim i, lastrow As Long
    
    lastrow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    
    If MsgBox("Are you sure you want to delete the row?", vbYesNo + vbQuestion, "Delete row") = vbYes Then
'Other code
Dim temp As Variant
For i = 1 To lastrow
    If Cells(i, 6) = 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, 6), Cells(Rows.Count, 3).End(xlUp)).ClearContents
            Cells(i, 6).Resize(UBound(temp, 1), 2).Value = temp
        Else
            Range(Cells(i, 6), Cells(Rows.Count, 3).End(xlUp)).ClearContents
        End If
        Exit For
    End If
Next i
'Other code
    End If
End Sub

Code: Select all

For i = 1 To lastrow
    If Cells(i, 6) = 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, 6), Cells(Rows.Count, 3).End(xlUp)).ClearContents
            Cells(i, 6).Resize(UBound(temp, 1), 2).Value = temp
        Else
            Range(Cells(i, 6), Cells(Rows.Count, 3).End(xlUp)).ClearContents
        End If
        Exit For
    End If
Next i
 

Code: Select all

Private Sub CommandButton1_Click()
    Dim i As Integer
    
    For i = 1 To Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    If Cells(i, 1) = ListBox1.List(ListBox1.ListIndex) Then
        Rows(i).Select
        Selection.Delete
    End If
    Next i
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim i As Integer
    For i = 1 To Sheets("Preview").Range("A" & Rows.Count).End(xlUp).Row
        If Sheets("Preview").Cells(i, 1).Value = ListBox1.List(ListBox1.ListIndex) Then
            Sheets("Preview").Rows(i).EntireRow.Delete
        End If
    Next i
End Sub