## Macro to hide cells based on outcome

Creating a macro - Writing a Script - Using the API

### Macro to hide cells based on outcome

So, I've been tasked with creating an automated calculation for my work. I'm not a novice at excel, but I am with macros.

Ideally, I'd like for cells to be hidden based on the three possible outcomes determined in cell D39 [=IF(B36>E9,"MOVE TO PWD DISBURSEMENT TRACKING SHEET",IF(E9=B36,"STOP! NO FURTHER ACTION NECESSARY",IF(B36<E9,"MOVE TO STEP 4. Aid to be Disbursed or Returned")))]

If "MOVE TO PWD DISBURSEMENT TRACKING SHEET" - Rows 42-82 should be hidden
If "STOP! NO FURTHER ACTION NECESSARY" - Rows 42-103 should be hidden
If "MOVE TO STEP 4. Aid to be Disbursed or Returned" - Rows 83-103 should be hidden

Jason
Office 2010, Windows 7
jcc1981

Posts: 2
Joined: Tue Mar 07, 2017 6:37 pm

### Re: Macro to hide cells based on outcome

Hide cell contents in Calc Spreadsheets:

https://forum.openoffice.org/en/forum/viewtopic.php?f=101&t=54749

If you're after help with Excel, this forum maybe:

http://www.vbforums.com/forumdisplay.php?37-Office-Development
Openoffice 4.1.2
Windows 8
JeJe

Posts: 221
Joined: Wed Mar 09, 2016 2:40 pm

### Re: Macro to hide cells based on outcome

Hi, and welcome to the forum.

jcc1981 wrote:I'd like for cells to be hidden based on the three possible outcomes determined in cell D39
If you want cell contents conditionally hidden, use Format → Conditional Formatting and a cell style which specifies number format ;;; (three semicolons).

But your explanation suggests that you want rows to be hidden, not cells. The standard way in Calc to hide cells is with a filter. Data → Filter → Advanced filter will do what you need without the need for evil macros. Open the attachment. Change either B36 or E9. The text in D39 will change accordingly. To hide the desired rows, click A41 and use Data → Refresh Range.

If you need the latter actions automated, use Tools → Macros → Record Macro to record clicking A41 and refreshing. Also see [Tutorial] Binding a macro: Shortcut key, menu or toolbar.

If you want the rows to hide/unhide automagically, without a range refresh see [Tutorial] Sorting and Filtering data with formulas.

Read about conditional formatting and/or recording macros in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum.

[Tutorial] Ten concepts that every Calc user should know

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Attachments
201703071420.ods
Last edited by MrProgrammer on Wed Mar 08, 2017 7:46 pm, edited 1 time in total.
Mr. Programmer
AOO 4.1.5 Build 9789 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).

MrProgrammer
Volunteer

Posts: 3329
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Macro to hide cells based on outcome

Thanks for the information!!
Office 2010, Windows 7
jcc1981

Posts: 2
Joined: Tue Mar 07, 2017 6:37 pm