Hide Zero Rows or Zero Columns in a Worksheet

Closed
Report
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
Hello,


I would like to know if there is VB code out there that solves for Hiding rows and Columns that show either zeros across the rows or the Columns have zero or null for formula results.

Thanks


1 reply

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Doe,

What range are we talking about? Could you post some sample data how it is now and how you foresee the result to look like?

If A1 is zero then hide row 1 or column A?

Best regards,
Trowa
0
In some cases the rows return zero all the way across, I would like to conditionally suppress that row when the formula returns 0. And if the columns do not return data or are zero because the month has yet to have values I would like to conditionally suppress those columns.

This is a month to month income statement show as an example, but if I can get one, we can get many.

I copied to of my team members. If we can put VB in our worksheet to range the cells that would avoid clunky conditional formats that only return white-out cells with column headers. There is a lot up on various forums on this, but if you had code or a step by step that sure would help.

Truly,


Doris


YTD January February March April May June July August September October November December
REVENUES:
Escrow Fees 13,248,898.9 668,583.92 809,970.99 1,163,815.73 1,318,352.10 1,177,574.83 1,257,692.37 1,331,976.27 1,111,628.50 1,258,727.31 1,172,794.70 872,868.25 1,104,913.91
Escrow REO Fees 413,930.9 17,030.00 29,722.90 39,847.25 42,308.42 33,493.50 29,377.35 50,285.63 51,105.77 33,851.82 33,940.08 31,079.48 21,888.70
Escrow Ref Fees 205,478.2 9,400.00 15,020.00 11,314.00 14,425.00 15,175.00 15,145.00 21,625.00 13,297.34 20,180.00 16,779.00 21,682.00 31,435.85
Escrow Directed Fees 20,099.0 0.00 0.00 0.00 0.00 0.00 6,756.00 1,628.00 0.00 2,120.00 7,805.00 1,790.00 0.00
Escrow Fees - Document Handling Fee 0.0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Closing Fees 433,534.8 22,275.00 26,674.20 36,865.00 40,535.00 40,377.50 41,755.15 42,550.00 37,993.00 41,230.00 37,669.92 30,615.00 34,995.00
Pickford Analysis 0.0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Revenue - Reductions (Disc) 0.0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
TCE Analysis 0.0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
TCE Management Fee 0.0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Other Revenue 0.0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Escrow Losses (17,369.0) (4,242.78) (2,069.00) (2,804.50) (2,590.95) (162.50) 16.00 (2,650.00) (633.75) (113.80) (4,732.06) 3,057.37 (443.00)
Net Revenue 14,304,572.8 713,046.1 879,319.1 1,249,037.5 1,413,029.6 1,266,458.3 1,350,741.9 1,445,414.9 1,213,390.9 1,355,995.3 1,264,256.6 961,092.1 1,192,790.5
0
Hi I did not hear back...it was hard to paste the example in the notes field.
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Hi Doris,

Sorry to keep you waiting, but we had some troubles with hardware and system updates, so I couldn't do much.

Let me see if I get this right:
For each row that has " 0.00" in each cell in column B:M, you want that row to be hidden automatically.
And when you want to change that value into something else you manually unhide the row to do so.

That would mean that in your example 6 rows will be hidden.

Am I on the right track?

If not, then consider the option to post your workbook using a free file sharing site like ge.tt or www.speedyshare.com and then post back the download link.

In your workbook you could use one sheet with unprocessed data and another with processed data as if the code was already active.

Best regards,
Trowa
0