Hide row when a cell has a zero value

timmyfatlips

New Member
Joined
Aug 19, 2011
Messages
3
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
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
Code:
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

New Member
Joined
Aug 19, 2011
Messages
3
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,237
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Aug 19, 2011
Messages
3
I've got it working - and unsurprisingly, I was missing the obvious!
Thanks a lot for your help Michael.
Really appreciated.
t
 

Watch MrExcel Video

Forum statistics

Threads
1,108,753
Messages
5,524,654
Members
409,597
Latest member
Dannydev

This Week's Hot Topics

Top