Hiding rows based on value and a formula

Sweedler

New Member
Joined
Nov 13, 2020
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Hello all,

I have a bit of a conundrum here tha tI am hoping someone can help me with. I have a spreadsheet (Budget) with several rows and a 6 columns. I want to assign a toggle button that allows me to hide a row when the sum value of the column B,C,D,E,F,G (for any given row) is equal to zero. I basically want to be able to toggle those zero result rows away when I save as a PDF and then back again. Those that make sense.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
I think the easiest way is to create a calculated column that sums up columns B-G, and then simply apply Filters on this column, where you can elect to easily hide or display the 0 rows.
 

Sweedler

New Member
Joined
Nov 13, 2020
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
I think the easiest way is to create a calculated column that sums up columns B-G, and then simply apply Filters on this column, where you can elect to easily hide or display the 0 rows.
True,

That is of course one way, and with a slicer it would be a button of sorts. All the same I am hoping to find a way to do it with a toggle switch and a MACRO.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
That is of course one way, and with a slicer it would be a button of sorts. All the same I am hoping to find a way to do it with a toggle switch and a MACRO.
I would still recommend using the calculated column and Filters, and toggling back and forth.
You can use Macros to do all of that. The Macro Recorder can help you get a lot of the code that you require.

The other option with Macros is to loop through all the data, row-by-row. It will work just fine, but loops are notoriously slower and more expensive in VBA.

Let us know which way you decide to go, and if you need help with any of that.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,096
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top