VBA to auto populate a table from data entered in another table-CodePudding

I'm an electrical contractor and I made a worksheet to help me bid projects. Say I'm bidding on wiring a new house. I have broken down each task "outlet"/"Switch" to materials and labor needed for each task. Those materials are then multiplied by the quantity needed and populate 3 different tables automatically.

"Rough_Materials" - Sheet5 A1:B120 - Header - YES

"Trim_Materials" - Sheet5 D1:E120 - Header - YES

"Service_Materials"- Sheet5 G1:H120 - Header - YES

What I am trying to do is populate rows in EACH table where materials are needed. Essentially consolidate the data in EACH table by eliminating rows with quantities of 0 and fill down rows with material needed: without deleting rows of the other tables.

I've tried so many different things I can barely think straight anymore. Been working on this for over a week. I know that the range isn't right on this example, that is definitely a problem. I got this from the internet and have been trying to adjust it to my scenario. All in all, this isn't my forte and I've reached the limit of patience with myself. Any help would be extremely appreciated!

Sub DeleteRowsBasedonCellValue()

    'Declare Variables
    Dim LastRow As Long, FirstRow As Long
    Dim Row As Long

    With ActiveSheet
        'Define First and Last Rows
        FirstRow = 1
        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'Loop Through Rows (Bottom to Top)
        For Row = LastRow To FirstRow Step -1
            If .Range("A" & Row).Value > 0 Then
                .Range("A" & Row).Delete
            End If
        Next Row
    End With

End Sub

CodePudding user response：

If I understand your question correctly, what you are looking for is something like this:

Sub DeleteRowsBasedonCellValue()
    'Declare Variables
    Dim LastRow As Long, FirstRow As Long
    Dim Row As Long
    Dim columns As Variant, column As Variant
    
    columns = Array("A", "D", "G")
    
    With ThisWorkbook.Worksheets("Sheet1") '<- type the name of the Worksheet here
        'Define First and Last Rows
        FirstRow = 1
        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        
        'Loop Through Columns
        For Each column In columns
            'Loop Through Rows (Bottom to Top)
            For Row = LastRow To FirstRow Step -1
                If .Range(column & Row).Value = 0 Then
                    .Range(column & Row).Resize(1, 2).Delete xlShiftUp
                End If
            Next Row
        Next column
    End With
End Sub

Test it out and see if this does what you want.

Alternatively, it might be wiser to be more explicit and make the code more flexible. If your tables are actually