Hide rows based on the value of one of their cells

[Museau]

Hello everyone!

First off, I should start with a big, big thank you: I've spent a few days working on a template to create offers for my catering business, and this website has helped me SO MUCH! However, it seems that I can't make this one work, even by reading other posts:

On the budget page of the offer, I would like to hide all rows where the number of the "d" column (which is the quantity of an item) is "0". So if for instance d4=0, then I'd like to hide the whole d row. Is there an easy way to make this using VBA?

Have a great day!

Museau

 

[Sektor]

Why don't you use Autofilter?

 

[Museau]

Hey Sektor,

Thanks for your answer! I was trying to avoid this and automate the process, is there a way to do this automatically in real time? Or would it slow down the file too much?

 

[Sektor]

Autofilter works much faster comparing to manual hiding.
Of course, you can automate it with VBA. Suppose, you have a range A1:F10 with header. Then the following code will filter out zeros in column D:

Sub DoFilter()
    'First, remove any filter if it was previously applied.
    ActiveSheet.AutoFilterMode = False
    'Do the filtering.
    'The field number is ALWAYS relative to the range - not the sheet!
    'Column "D" is fourth column relative to A1:F10.
    With Range("A1:F10")
        .AutoFilter Field:=4, Criteria1:=0
    End With
End Sub

 

[Museau]

Whow thank you so much! It seems to work, but now it only shows the lines where the qty is zero. This is probably a very obvious question but how can I do it so that it HIDES the rows with zero in column D?

 

[Sektor]

Oops! Sorry
Criteria1:="<>0"

 

[Museau]

Thank you VERY MUCH! That works great and I can keep on working on the rest now. [TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD="bgcolor: #FFFFFF"]Большое спасибо!

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sektor]

Большое спасибо!

Not at all!