Hiding certain rows depending on cell value

[Phil360]

Hi all,

I'm fairly new to VBA programming. Im actually creating a BOM (Bill of Material) in Excel, and I'm simply trying to hide the rows where the item quantity is 0 when clicking a command button. It looks like this:

Private Sub CommandButton1_Click()


If Worksheets("BOM").Range("D3").Value = 0 Then
Worksheets("BOM").Rows("3:3").Hidden = True
End If


If Worksheets("BOM").Range("D4").Value = 0 Then
Worksheets("BOM").Rows("4:4").Hidden = True
End If


If Worksheets("BOM").Range("D5").Value = 0 Then
Worksheets("BOM").Rows("5:5").Hidden = True
End If


If Worksheets("BOM").Range("D6").Value = 0 Then
Worksheets("BOM").Rows("6:6").Hidden = True
End If

.....

It works fine, but there is over 300 rows. Is there a way to do a similar command for all the rows?

Thanks,

Phil

 

[Joe4]

Welcome to the Board!

This can be done pretty easily using Filters.

[COLOR=#333333]Private Sub CommandButton1_Click()
[/COLOR]

    Dim myRange As Range


'   Select all rows in column D with data
    Set myRange = Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    
'   Filter
    myRange.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd
    
End Sub

 

[Phil360]

Thank you very much! Problem solved.