Compare Cell with Cell above VBA Excel

Question

I am new here.

I am trying to build a quick VBA program to "flatten" a Bill of Materials by heirarchy (BOM Level) and Status.

Here is some sample data:

The sample data shows a BOM with a Car as a top level assembly, Wheel and Engine as second level assemblies, and various children parts that make up those assemblies on the third and fourth level of the BOM.

I want to delete any rows that have the value "ZE", "ZM", or blank in column C.

I also want to delete any rows that have the value "ZA" and are also direct children of another "ZA" item. (Example - Delete the Rim row from the BOM because the Wheel is the Parent "ZA" item)

Here is what I have so far:

Sub deletechildren()

 Dim lr As Long, i As Long, k As Long

    lr = Cells(Rows.Count, 1).End(xlUp).Row

    For i = lr To 1 Step -1
        If i > 2 Then
            k = i - 1
        End If
        If Cells(i, 3).Value = "ZA" And Cells(i, 1).Value = Cells(k, 1).Value Then
            Cells(i, 3).EntireRow.Delete
        ElseIf Cells(i, 3).Value = "ZE" Then
            Cells(i, 3).EntireRow.Delete
        ElseIf Cells(i, 3).Value = "ZM" Then
            Cells(i, 3).EntireRow.Delete
        ElseIf Cells(i, 3).Value = "" Then
            Cells(i, 3).EntireRow.Delete
        End If
    Next i

    lr = Cells(Rows.Count, 1).End(xlUp).Row

End Sub

I am getting some error on the first part of the If statement, where I want to parse out any "ZA" status children from the "ZA" parent.

Any ideas?

What is the value of `k` when the loop first hits that If statement? — zack.lore, May 10 '16 at 17:02
Sorry, the first If statement was just something I was toying with. Realistically, "k" should always be "i-1". I was trying to use "k" as the identifier for the row directly above the current row being assessed, "i". — R Mar, May 10 '16 at 17:11
What I was getting at is that `k` may not have been initialized before that iteration of the loop. Are you sure that k has a value on the first time it hits the `If` statement? — zack.lore, May 10 '16 at 17:13
Try adding a `k = 0` value in there before your loop. I know it *should* be getting a value since `lr` should be greater than 2, but if you step through your macro (use F8), when it gets to the first `If Cells(i,3)...` line, hover your mouse over `k`, and see if it has a value pop up. (Or do `?k` in the Immediate Window) — BruceWayne, May 10 '16 at 17:26
I know not solving your problem, but definitely try and use proper variable naming conventions. It makes a huge difference when trying to work with your code or debugging. lr, i, k, could be anything. — StormsEdge, May 10 '16 at 17:27
Ok, to initialize the constant "k", I set k=0 outside of the loop. I am getting a run-time error '1004' - application defined error. — R Mar, May 10 '16 at 17:28
When I test with your data I get no error. How are you triggering this code within excel? — zack.lore, May 10 '16 at 17:40
Developer tab > Visual Basic > Module > Paste Code > Run Sub — R Mar, May 10 '16 at 17:44
You might want to have a look at the following:http://stackoverflow.com/questions/36873359/fastest-way-to-delete-rows-which-cannot-be-grabbed-with-specialcells There you will find a few different examples on how to delete rows fast. The only difference you will have to adjust for is comparing a row with the one above. But that should be fairly easy to implement. — Ralph, May 10 '16 at 17:45
Code runs and is able to delete the cells with "ZE", "ZM" and blanks without an issue. I want to be able to delete the Rim row because it is both a "ZA" status item and it is a child of another "ZA" status item. I think my If statement with the two conditions is written incorrectly and I am reaching out for help. — R Mar, May 10 '16 at 17:48

score 1 · Answer 1 · answered May 10 '16 at 18:06

Sub DeleteChildren()

Dim lastRow As Long
Dim i As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastRow

    If (Cells(i, 3).Value = "ZE" Or Cells(i, 3).Value = "ZM" Or Cells(i, 3).Value = "") And Cells(i, 1) <> "" Then
        Rows(i).EntireRow.Delete xlShiftUp
        i = i - 1
        GoTo NextIteration
    End If

    If Cells(i, 1).Value > 1 Then
        If (Cells(i, 3).Value = "ZA" And Cells(i - 1, 3).Value = "ZA") And Not Cells(i, 1).Value < Cells(i - 1, 1).Value Then ' This way is a there are multiple levels with "ZA" there can
            Cells(i, 5).Value = "Delete"
        End If
    End If

NextIteration:
Next i

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastRow
    If Cells(i, 5).Value = "Delete" Then
        Rows(i).EntireRow.Delete xlShiftUp
        i = i - 1
    End If
Next i

End Sub

score 0 · Accepted Answer · answered May 10 '16 at 17:42

A part of the problem is that the variable k is not being used to correctly identify parent/children relationships (if I understand your requirements correctly). In your case, you are comparing the each value with the row above it, but in a bill of materials, the parent row might be multiple rows above, and is denoted by a hierarchy value - 1.

See my revised code below. In the code, we first delete any rows that we know to delete (ZM, ZE, and Blanks). Next, we loop up the hierarchy values until we find one hierarchy value above the current row. That becomes the parent row, and from there, we test.

Let me know if you need additional help.

Sub deletechildren()

 Dim lr As Long, i As Long, k As Long

    lr = Cells(Rows.Count, 1).End(xlUp).Row

    For i = lr To 1 Step -1
        If i > 2 Then
            k = i - 1
            If Cells(i, 3) = "ZE" Or Cells(i, 3) = "ZM" Or Cells(i, 3) = "" Then
                Rows(i).Delete
            Else
                k = i - 1
                Do Until i <= 2 Or (Cells(i, 1) - Cells(k, 1) = 1)
                    k = k - 1
                Loop

                'Now, k represents the parent row.
                If Cells(i, 3) = "ZA" And Cells(k, 3) = "ZA" Then
                    Rows(i).Delete
                End If

            End If
        End If

    Next i

    lr = Cells(Rows.Count, 1).End(xlUp).Row

End Sub

score 0 · Answer 3 · answered May 10 '16 at 17:47

I'd use Autofilter() and Sort() methods of Range object like follows:

Option Explicit

Sub deletechildren()

    Dim i As Long

    With Worksheets("BOM")

        With .Range("A1:D" & .Cells(.Rows.Count, 1).End(xlUp).Row)

            .AutoFilter Field:=3, Criteria1:=Array("ZE", "ZM", "="), Operator:=xlFilterValues                
            With .Offset(1).Resize(.Rows.Count - 1)
                If Application.WorksheetFunction.Subtotal(103, .Columns(1)) > 1 Then .SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End With
            .AutoFilter

            .Sort key1:=Range("C1"), order1:=xlAscending, key2:=Range("A1"), order2:=xlAscending, Header:=xlYes

            i = .Rows(.Rows.Count).Row
            Do Until .Cells(i, 1) = .Cells(2, 1)
                i = i - 1
            Loop
            If i < .Rows.Count Then .Rows(i + 1).Resize(.Rows.Count - i).EntireRow.Delete

        End With

    End With

End Sub

Compare Cell with Cell above VBA Excel

3 Answers3