Delete all but one row based on two column values in VBA

Question

I am new to VBA and want to remove rows from an Excel file. I want to remove a row that has a Status 'Completed', but only if there is at least one more row remaining for a CustomerName. In other words, if there is only one more row remaining for a certain CustomerName, it should not be deleted, even though it might have Status 'Completed'.

I figured out how to remove rows if they have Status = 'Completed':

Sub RemoveAlmostAllCompletedRows()
Dim i As Long

i = 1

Do While i <= ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Rows.Count

If InStr(1, ThisWorkbook.ActiveSheet.Cells(i, 18).Text, "Completed", vbTextCompare) > 0 Then
    ThisWorkbook.ActiveSheet.Cells(i, 1).EntireRow.Delete
Else
    i = i + 1
End If

Loop

End Sub

This removes all rows that have 'Completed' in column 18 (Status column). Now I am really stuck finding a way to prevent a row with Status = Completed, if that would delete the last remaining row for a CustomerName.

Any ideas or tips to get me going? Your help is much appreciated!

refer to [this](https://stackoverflow.com/questions/44928753/macro-code-to-number-lines-based-on-if-there-is-data/44929356#44929356) union method is fast and simple. — Dy.Lee, Oct 06 '17 at 14:23
@Dy.Lee Did you read the question? Fraukje, in combination with the `instr`, also use `application.worksheetfunction.countif()` on the CustomerName column to ensure the value is >1. Also a note on deletions and loops. When deleting ideally you want to loop in reverse, as deleting i technically makes i+1 the new i. you could do `for var = ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Rows.Count to 2 step -1` — Zerk, Oct 06 '17 at 14:26
@Zerk, In large data, your method is slow. Union method collect range, and at one time delete all range. It is very fast. — Dy.Lee, Oct 06 '17 at 14:32
@Dy.Lee You're really not understanding the question. Union is faster for deletions as it is only a single transaction and I'd recommend it in most cases. It is not compatible with the question. Assuming a customer has 3 completed orders, all 3 would get deleted as the countif would return 3 for each test due to the rows not being deleted until the end. Deleting individually ensures that when only one remains it is kept. — Zerk, Oct 06 '17 at 14:35
@Zerk, you are right. But questioner's explanation seems to be lacking. — Dy.Lee, Oct 06 '17 at 15:43

score 1 · Accepted Answer · answered Oct 06 '17 at 14:30

You could use the WorksheetFunction.CountIf. Considering that the column A:A is the one containing the customer names and worksheet Sheet1 is the sheet you're working in:

Sub RemoveAlmostAllCompletedRows()

    'You should always define precisely your worksheet
    Dim WS As Worksheet
    Set WS = ThisWorkbook.Worksheets("Sheet1")

    Dim i As Long
    i = 1

    Do While i <= WS.Range("A1").CurrentRegion.Rows.Count

        If (InStr(1, WS.Cells(i, 18).Text, "Completed", vbTextCompare) > 0) Then
            If (Excel.Application.WorksheetFunction.CountIf(WS.Range("A:A"), _
                WS.Range("A" & i).Value2)) > 1 Then

                WS.Cells(i, 1).EntireRow.Delete

            End If
        Else
            i = i + 1
        End If

    Loop

End Sub

Thanks a lot for pointing me towards the CountIf function. Also, showing me that worksheet definition is required is much appreciated! — Fraukje, Oct 06 '17 at 14:39
@Fraukje; My pleasure and thanks for your comment. In fact, worksheet definition is not "required" but it is a best practice that will help you avoid a lot of future problems. You could check **[this post](https://stackoverflow.com/a/10717999/2687063)** for more details. — simpLE MAn, Oct 06 '17 at 14:44

score 1 · Answer 2 · answered Oct 06 '17 at 14:32

Place the below line inside the first IF block as another IF block before deleting the row.

If Application.WorksheetFunction.Countif(ThisWorkbook.ActiveSheet.Cells(i,10).EntireColumn),ThisWorkbook.ActiveSheet.Cells(i,10).Value) > 1 Then

This will count the customer names and only call delete line if there is more than 1. I made assumption customer name is in column 10. Change as needed.

Delete all but one row based on two column values in VBA

2 Answers2