delete one extra row

Hi Experts

How would you make the current macro delete on extra row......in addtion to its current functionality..

here is the VBA Code:

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim Sheet As New Collection
Dim rng As Range
Dim Finished As Boolean
Dim lastfound As Long
Dim rngRow As Long
Dim confirm As Boolean
Dim firstSupplierRow As Range
Dim lastSupplierRow As Range
Dim aSupplier As Range
Dim foundDemand As Boolean
    confirm = MsgBox("ARE YOU SURE YOU WANT TO DELETE SUPPLIER :> " & Me.Controls("combobox1").Text, vbYesNo, "Delete Supplier") = vbYes

    If confirm Then
        'Populate affected worksheets
        Sheet.Add ThisWorkbook.Worksheets("Deal Selection")
        Sheet.Add ThisWorkbook.Worksheets("Tables")
        Sheet.Add ThisWorkbook.Worksheets("Alloc (sc.1)")
        Sheet.Add ThisWorkbook.Worksheets("Alloc (sc.2)")
        Sheet.Add ThisWorkbook.Worksheets("Alloc (sc.3)")
        Sheet.Add ThisWorkbook.Worksheets("Modelling (Vol)")
        Sheet.Add ThisWorkbook.Worksheets("Allocation (Vol)")
        Sheet.Add ThisWorkbook.Worksheets("CashFlow Yearly")
        Sheet.Add ThisWorkbook.Worksheets("CashFlow Q4")
       
        For Each ws In Sheet
            If ws.Name = "Deal Selection" Then
                Set rng = ws.Range("B:B").Find(Me.Controls("combobox1").Text)
                rng.Resize(1, 6).Delete xlShiftUp
            ElseIf ws.Name = "Tables" Then
                Set rng = ws.Range("J:J").Find(Me.Controls("combobox1").Text)
                rng.Delete xlShiftUp
            ElseIf ws.Name = "CashFlow Q4" Then
                Set rng = ws.Range("B:B").Find(Me.Controls("combobox1").Text)
                lastfound = 1
                Finished = False
                Do While Not rng Is Nothing And Not Finished
                    rngRow = rng.Row
                    lastfound = rng.Row
                    Do While ws.Range("B" & rngRow) = Me.Controls("combobox1").Text
                        ws.Range("B" & rngRow).EntireRow.Delete
                    Loop
                    ws.Range("B" & rngRow).EntireRow.Delete
                    ' Delete this instance
'                    rng.EntireRow.Delete
                    Set rng = ws.Range("B:B").Find(Me.Controls("combobox1").Text)
                    Finished = rng Is Nothing
                Loop
                ws.Range("B" & ws.Rows.Count).End(xlUp).Resize(1, 52).Borders(xlBottom).Weight = xlMedium
                ws.Range("B" & ws.Rows.Count).End(xlUp).Borders(xlBottom).LineStyle = xlContinuous
            Else
                Set rng = ws.Range("B:B").Find(Me.Controls("combobox1").Text)
                lastfound = 1
                Finished = False
                Do While Not rng Is Nothing And Not Finished
                    rngRow = rng.Row
                    lastfound = rng.Row
                    Do While ws.Range("B" & rngRow) = Me.Controls("combobox1").Text
                        ws.Range("B" & rngRow).EntireRow.Delete
                    Loop
                    ' Delete this instance
'                    rng.EntireRow.Delete
                    Set rng = ws.Range("B:B").Find(Me.Controls("combobox1").Text)
                    Finished = rng Is Nothing
                Loop
                ws.Range("B" & ws.Rows.Count).End(xlUp).Resize(1, 52).Borders(xlBottom).Weight = xlMedium
                ws.Range("B" & ws.Rows.Count).End(xlUp).Borders(xlBottom).LineStyle = xlContinuous
            End If
        Next
    Else
   
        MsgBox Me.Controls("combobox1").Text & " - CUSTOMER NOT ADDED", , "USER CANCEL"
   
    End If
    Unload Me

End SubStart Free Trial