Return a bill of material that's created on another sheet without any lines that = 0

[Ecustis]

Hi all,

This has been driving me crazy. Basically, I'm trying to build a bill of material on a sheet that's separated by vendor and then display every line item that has a quantity value of >=1 on another sheet.

For example, this would be my bill of material list on a separate sheet:

Qty	Type	Description	Price	Total
1	Panel	Generic Description	$100	=D2*A2
0	Wire	Generic Description	$200	=D3*A3
5	Panel	Generic Description	$200	=D4*A4
2	Panel	Generic Description	$500	=D5*A5

Then, I would want this main sheet to display every line item that is >=1, so row 3 wouldn't be displayed on this sheet.
My main sheet would then look like this

Qty	Type	Description	Price	Total
1	Panel	Generic Description	$100	$100
5	Panel	Generic Description	$200	$1000
2	Panel	Generic Description	$500	$1000

Is this even possible? I feel like anything can be done with excel, but it's driving me crazy not being able to figure it out. I've searched every possible term I could think of on google to try and see who's already accomplished this before, but no luck.

Any help is greatly appreciated. Thanks!

 

[Jamie McMillan]

Hello Ecurtis,

This should do what you want. Simply it, unhides all the rows and clears the data, then copies the data from Sheet9 and pastes it into A1; then hides the rows with a 0 value.
You need to Change:- Sheets("Sheet9").Range("A1:E4").Copy to your sheet name and range, Range("A1:E4").ClearContents to a similar range.
Then you apply the macro to a shape, when the data changes; just run it again.

Sub Material()
Dim i As Integer
i = 1
    Cells.EntireRow.Hidden = False 'unhides all the rows
    Range("A1:E4").ClearContents 'clears current data
  
    Sheets("Sheet9").Range("A1:E4").Copy 'copies data from other sheet
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False 'pastes values to this sheet

    For i = 1 To 20
    If Cells(i, 1).Value <= 0 Then
    Cells(i, 1).EntireRow.Hidden = True 'hides all the rows with a 0 in column A
    End If
Next i
End Sub

NB Set the shape properties to do non move or resize with cells - the shape may get hidden.

Jamie