Hide rows to create summary

[SteveG31530]

We have a program that dumps data into XLS. There are two end-user
requirements for the output. One requirement is essentially a
shortened version of the other.

To ensure that the full data set is always present it struck me that I
should be able to hide rows to create the summary. The default
version of the output will be the "short" version. The idea is that
the "expert" version would become available by unhiding the lines.

I have seen samples of code

http://www.vbaexpress.com/kb/getarticle.php?kb_id=416

with expressions like

Rows(4).EntireRow.Hidden = True

What is the simplest way to hide a mix of contiguous and contiguous
lines? ie. Hide lines 3-4, 6 ,8-22, 25 and 31. There might be 100
hidden lines in total. Does each line have to be hidden individually
or can the be treated as groups?

My VBA skills are definitely in the beginner category!

Steve

 

[Rick Rothstein]

You can do it with a single statement, but you must show a colon delimited
start and end row for each entry in the list (just use the same start and
end row for single row items (for example, your single Row 6 item would be
shown as 6:6)...

Range("3:4,6:6,8:22,25:25,31:31").EntireRow.Hidden = False

 

[SteveG31530]

This works really well!

Is there a way to trigger the macro when the .xls is opened?

Thanks

Steve

 

[Rick Rothstein]

I see you worked through my accidentally using False instead of True in the
statement (I reset the visibility of the rows and then accidentally copied
that statement by mistake).

As for your question, I guess you can use the workbook's Open event to do
that. Right click the small icon to the left of the word File in Excel's
menu bar (this takes you to the code window for the Workbook) and copy paste
the following into the code window that opens up...

Private Sub Workbook_Open()
Range("3:4,6:6,8:22,25:25,31:31").EntireRow.Hidden = True
End Sub

 

[SteveG31530]

This is even better.

Now we have two end-users satisfied with one output.

Many thanks

Steve