Conditional hiding of rows

[K. Georgiadis]

I have a workbook containing multiple product pricing
calculations, each calculation consisting of 18 rows and
3 columns (some 40 calculations in total). Each
calculation can be a "two step" or "three step"
calculation, depending on the needs of the user.

Depending on whether the user chooses "two step," I want
to be able to hide three rows that are only applicable
to "three step" calculations. Can this be programmed as a
separate option for each of the 40 calculations?

My back up plan is to use conditional formatting and
black out the unneeded rows.

 

[icestationzbra]

have you explored the possibility of using filters?

you can get good information on filters, both autofilters and advance
fitlers, at www.contextures.com

 

[K. Georgiadis]

Aren't filters intended for lists? I'm dealing with
caculations (gross price, less freight, less discounts,
sort of thing)

 

[Dave Peterson]

Depending on how they indicate 2-step or 3-step, this might work:

I used A1 as the cell that is used as the indicator and rows 13-15 as the rows
to hide/show:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

Me.Rows("13:15").Hidden = CBool(Target.Value = 2)

End Sub

And I assumed that they typed 2 or 3 in A1.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the code window.

Then back to excel and type something in A1 of that worksheet.

 

[K. Georgiadis]

Thanks for the subroutine. The complication is that not
every calculation is in a separate worksheet. In many
cases, a worksheet may have as many as 5 separate
calculations, each with its own indicator as to whether
it is a 2- or a 3-step calculation.

 

[Dave Peterson]

You could add code behind each worksheet that looks for that indicator.

If the worksheets are laid out consistently, maybe even the
workbook_worksheetchange event???