Hide row when a cell has a zero value

[timmyfatlips]

Hello there,
Apologies for posting this question when I see that similar questions have been posted elsewhere. Unfortunately, my knowledge of VB is very limited and I haven't been able to get the (seemingly very clear) responses to those other questions to work.

I have a spreadsheet that is processing a set of data that is being imported from FileMaker Pro.

One of the worksheets is a long list of data, and the value in column B is either 0 or a text string that is being copied over from another worksheet.
What I would like to be able to do is to hide the majority of those rows in Column B that have 0 values (and then unhide them should the value change to text), whilst a few (which have titles in) will need to be kept whether they have zeros or not.

The entire range is B3:B784. And the cells that need to be excluded from the formula (that don't cause the row to hide if they have a 0 value) are B30, B58, B86, B114, B142, B170, B198, B226, B254, B282, B310, B338, B366, B394, B422, B450, B478, B506, B534, B562, B590, B618, B646, B674, B702, B730, B758.

I'd like the worksheet to hide those cells that have zero values, but unhide them when I refresh the data with that from Filemaker if they no longer have that zero value.
And because the data in Filemaker is being updated quite regularly, it would be good if this hiding/unhiding could happen everytime I refresh the data.

I'm using Excel for Mac 2011, which I think has added to my confusion when trying to follow the instructions that I found elsewhere.

I'm really struggling with this; any help would be very, very much appreciated.

Many thanks in advance,
tim

 

[Michael M]

Hi Tim and Welcome to the Board
I might be missing something but when you refresh the data won't overwrite the existing data ??
Also, is row 784 the "last" row ?
If so, try this

Sub reds()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "B").End(xlUp).Row
For r = lr To 3 Step -1
    Rows(r).EntireRow.Hidden = False
    If Range("B" & r).Value = 0 Then Rows(r).EntireRow.Hidden = True
Next
Application.ScreenUpdating = True
End Sub

 

[timmyfatlips]

Brilliant Michael, many thanks for getting back so quickly.

You're right, row 784 is the last one.
As I understand it, refreshing the data with whatever's in Filemaker will overwrite the existing data. Would that be a problem?

And forgive my ignorance, but like I say I'm a VB beginner, and can you advise me on where the code you've given needs to be entered?

As I say, I'm using Excel for Mac 2011, and have gone to Tools / Macro / Visual Basic Editor, double clicked on the worksheet that I want this to apply to, and then pasted the code in the window that comes up.
When I close that window though, it's as if nothing has happened.

I'm sure I'm missing something very basic and fundamental, but any further help would be very much appreciated.

Thanks again,
tim

 

[Michael M]

Tim
No, the overwrite isn't a problem.
I don't know anything about Macs and their operation, so can't help there, but you can put the code in the "This Workbook" module and then put a button / shape, etc on the worksheet and assign the macro to it by right clicking on the button / shape and selecting assign macro, then picking the macro from the popup list that appears. Then once the new data is in, simply click on the Button / shape.

 

[timmyfatlips]

I've got it working - and unsurprisingly, I was missing the obvious!
Thanks a lot for your help Michael.
Really appreciated.
t