How to hide columns if value is zero -- after filtering data

Inventory reconciliation.xlsmI have seen a few suggestions on the forum on how to use a cell range to determine if a cell was zero, to then hide the cell.

The challenge I'm having is that I have a very wide spreadsheet of parts. Also pretty long list of locations. I need to filter the data for the exact location in question first, and that gives me three rows of data. 5 rows total show at that point. If the net result of those three rows in the column is zero, I'd like to run a macro to hide the unnecessary columns and collapse the sheet down to what I need to see/print. This would help us not side scroll for hundreds of items to find what we need.

I welcome suggestions on how to do this. Once the location is filtered to, I may be on rows 6-8, or I may be on 276-278. Is there some type of offset command I can use to assist? I welcome all input and have attempted to attach a sample sheet in question. I will typically sort by store number, and that will show you what I see from there on out.

Your explanation isnt enough with the amount of columns in that sheet. Why not create a row with a multiplication formula that multiples the rows you want to test and then filter out the 0s?

I went ahead and decided to sum up all lines to row 282. This allowed me to use this code to help:

Sub HideCols()

Dim rCell As Range

For Each rCell In Range("E282:CO282")
If rCell.Value = "0" Then
rCell.EntireColumn.Hidden = True
Else
rCell.EntireColumn.Hidden = False
End If
Next rCell

End Sub

I'm using row 282 values to determine if the column should be hidden. Doesnt' seem to work right on all columns yet. I have one that hides things it should not, and it appears to be related to values < 0.

The SUM function will sum values from all cells in its range regardless if the rows are hidden by filters or not. Try using the SUBTOTAL function instead. It respects filtered data.

=SUBTOTAL(109, E3:E281)

Can you tell me where you would place that in the above code? I'm not 100% sure I'm following and pretty new at this. Thanks.

Originally Posted by sirronstuff

Can you tell me where you would place that in the above code? I'm not 100% sure I'm following and pretty new at this. Thanks.

It replaces your SUM formulas in row 282.
Click the blue link to learn more about the SUBTOTAL function.
Your code would remain the same.

ok, then I don't want to use that function. I want to hide columns only if the filtered data still has a total of zero.

Originally Posted by sirronstuff

ok, then I don't want to use that function. I want to hide columns only if the filtered data still has a total of zero.

In cell C182 you have a SUM formula like this...
=SUM(C3:C181)
This will return the sum of all the cells in C3:C181 no matter if you filter the rows or not. It always returns the same sum regardless of filtering.

If you put this formula in cell C182
=SUBTOTAL(109, C3:C181)
...it only sums the visible filtered cells in C3:C181. It respects the filtered rows.

Then use your macro to hide values in row 182 that equal zero from the SUBTOTAL formulas. Is that what you want? If not, I don't understand.

I misunderstood. I will give that a try and see if it helps. Thanks.

Thank you! That appeared to work.