Hiding rows if the linked cells are unused

[K. Georgiadis]

I can't figure out how to solve the following problem using Excel's built in functions, so I am hoping that there is a VBA solution.

Attached is a zip file illustrating the challenge. This is intended to be a template that can accommodate up to 30 market segments:

1. In the worksheet named "Segment Data" there will be 30 segments, each with 3 sales scenarios. One of the 3 scenarios will be selected with a combo box and will be displayed on the "Active Scenario" row. The Active Scenario row will be appearing at 7-row intervals, as shown in the attached example

2. In the majority of cases, half (or fewer) of the segment blocks will be used but, because the "Active Scenario" row uses nested IF formulas, the row will not be blank but will contain zeros even if the data block is not used. For now, I am using the logic that if the sum total of all three scenarios for the planning period is zero, than it is safe to assume that the segment data block was not used.

3. The Active Rows are captured contiguously in "Sales Summary" and each column is aggregated using the SUM function.

The objective is to hide the unused rows, so as to make the table neat and compact, but to make them reappear automatically if the user later decides to populate a previously unused segment block.

Any suggestions?

THANX!!!!

[johnske]

Hi,

Is this any use? (It goes in the Sheet2 module)


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim N&, RowRange As Range, RowRangeValue&
Application.ScreenUpdating = False
For N = 5 To 35
Set RowRange = Range("B" & N & ":K" & N)
RowRangeValue = WorksheetFunction.Sum(RowRange)
If RowRangeValue <> 0 Then
Rows(N).EntireRow.Hidden = False
Else
Rows(N).EntireRow.Hidden = True
End If
Next N
Application.ScreenUpdating = True
End Sub


HTH,
John

[K. Georgiadis]

I'll give it a try and let you know. Thanks!

[K. Georgiadis]

I put the code in the Sheet2 module but it does not execute automatically. When I try to run manually, I am brought to a macro dialog box, where I am asked to enter which macro I am trying to run. What could be the problem?

Update: It does work if I add a SUM function in column L, summing up all the rows. I suppose I could add up the rows and hide column L, however, there is another problem:

If any linked row is zeroed out, or a previously unused row is subsequently populated, the SUM function has to be somehow manually re-run for the unused rows to hide properly. One way to "refresh" this is to go to the first cell containing the SUM function (L5) and copy it down AGAIN. Any way around this, so that the user does not have to take any action for the hiding/unhiding to occur correctly?

[johnske]

Hi,

1st try it as a calculate event, use


Private Sub Worksheet_Calculate()
instead of

Private Sub Worksheet_Change(ByVal Target As Range)


John

[K. Georgiadis]

Worksheet_Calculate seems to do the trick. I'll experiment with the worksheet a little more to confirm that there are no unforeseen result and, if everything continues to check out, I'll mark this as solved.


What I like about this is that the hidden rows can be anywhere in the table.

Thanks for your help so far. I am very optimistic that this will be declared "SOLVED"

[K. Georgiadis]

This seems to be working fine. There is something that I am curious about though: the "Undo" icon is disabled. Has that got something to do with the Worksheet-Calculate macro?

[johnske]

This seems to be working fine. There is something that I am curious about though: the "Undo" icon is disabled. Has that got something to do with the Worksheet-Calculate macro?

Hi,

The 'Undo icon' applies to worksheet level actions, not to actions performed by a VBA procedure - and the Worksheet_Calculate is a VBA procedure. To use Undo for a VBA procedure you must use it in a procedure - see your VBA Help files for more on this...

Regards,
John

[K. Georgiadis]

I did not want to include "Undo" within the VBA code. I was just curious why the "Undo" icon is disabled in this workbook but not in others.

Not a big deal, though. I can live with it.

It now works. I don't know why or how, but who cares!

[K. Georgiadis]

Thanks Johnske! I marked it "Solved"

:beerchug:

[johnske]

Not a prob. Glad to be able to help :thumb