CODEWITHSUNDEEP
vbaexcel
R Mar
R Mar

Reputation: 3

Compare Cell with Cell above VBA Excel

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: Sample BOM 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?

Upvotes: 0

Views: 3995

Answers (3)

Scott Ridings
Scott Ridings

Reputation: 844

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

Upvotes: 1

user3598756
user3598756

Reputation: 29421

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

Upvotes: 0

basodre
basodre

Reputation: 5770

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

Upvotes: 0

Related Questions