hide / unhide details
 

I have a spreadsheet with some "basic" data in rows and some "detail" data
in other rows. These two types of rows are intermixed throughout the
spreadsheet. I'd like to provide this data to others and include the
ability to easily hide and unhide the rows containing the "detail" data.
What's the best way to do that?

I have tried creating two views, two macros that point to those views, and
two buttons that point to those macros. That works except that I don't see
a way to modify the views as I add more data to the spreadsheet, and
therefore it appears that I have to recreate the views and macros everytime.
There must be a better way. Any ideas?

Thanks,
Chris Sites

hide / unhide details
 

In some worksheets, I use row 1 and column A to mark the data that I
want to hide.

Columns to be hidden have an "X" in row 1
Rows to be hidden have an "X" in column A

The following macros hide the detail, or show all the rows/columns:

'===============================
Sub HideDetail()
Rows("1:1").SpecialCells(xlCellTypeConstants, 23) _
.EntireColumn.Hidden = True
Columns("A:A").SpecialCells(xlCellTypeConstants, 23) _
.EntireRow.Hidden = True
End Sub
'================================
Sub ShowDetail()
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
End Sub
'==================================

Chris Sites wrote:
I have a spreadsheet with some "basic" data in rows and some "detail" data
in other rows. These two types of rows are intermixed throughout the
spreadsheet. I'd like to provide this data to others and include the
ability to easily hide and unhide the rows containing the "detail" data.
What's the best way to do that?

I have tried creating two views, two macros that point to those views, and
two buttons that point to those macros. That works except that I don't see
a way to modify the views as I add more data to the spreadsheet, and
therefore it appears that I have to recreate the views and macros everytime.
There must be a better way. Any ideas?

Thanks,
Chris Sites





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

hide / unhide details
 

A non-macro approach.

Select your range and do Data|filter|autofilter.

If you have to tell your users how to do it, it'll be time well spent. They'll
be able to do lots more useful stuff using filters.

Chris Sites wrote:

I have a spreadsheet with some "basic" data in rows and some "detail" data
in other rows. These two types of rows are intermixed throughout the
spreadsheet. I'd like to provide this data to others and include the
ability to easily hide and unhide the rows containing the "detail" data.
What's the best way to do that?

I have tried creating two views, two macros that point to those views, and
two buttons that point to those macros. That works except that I don't see
a way to modify the views as I add more data to the spreadsheet, and
therefore it appears that I have to recreate the views and macros everytime.
There must be a better way. Any ideas?

Thanks,
Chris Sites

--

Dave Peterson

hide / unhide details
 

That would work, but since my detail data is a different font color than my
basic data, would it be possible to simply use the font color as an
indicator instead of an additional column? What would the HideDetail macro
look like in that case? Thanks.

"Debra Dalgleish" wrote in message
...
In some worksheets, I use row 1 and column A to mark the data that I
want to hide.

Columns to be hidden have an "X" in row 1
Rows to be hidden have an "X" in column A

The following macros hide the detail, or show all the rows/columns:

'===============================
Sub HideDetail()
Rows("1:1").SpecialCells(xlCellTypeConstants, 23) _
.EntireColumn.Hidden = True
Columns("A:A").SpecialCells(xlCellTypeConstants, 23) _
.EntireRow.Hidden = True
End Sub
'================================
Sub ShowDetail()
Cells.EntireRow.Hidden = False
Cells.EntireColumn.Hidden = False
End Sub
'==================================

Chris Sites wrote:
I have a spreadsheet with some "basic" data in rows and some "detail"
data
in other rows. These two types of rows are intermixed throughout the
spreadsheet. I'd like to provide this data to others and include the
ability to easily hide and unhide the rows containing the "detail" data.
What's the best way to do that?

I have tried creating two views, two macros that point to those views,
and
two buttons that point to those macros. That works except that I don't
see
a way to modify the views as I add more data to the spreadsheet, and
therefore it appears that I have to recreate the views and macros
everytime.
There must be a better way. Any ideas?

Thanks,
Chris Sites





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

hide / unhide details
 

Hi Chris,
You would have to use a loop to check each cell in
Row 1 and in Column A. I am presuming you would
check to the font color of cell A1. I would try to check
to the value of an existing cell rather than trying to code
an actual colorindex value. Color differences on laptops,
especially in a font color are hard to see (see #gamma).
http://www.mvps.org/dmcritchie/excel/colors.htm#gamma

Sub HideIfFontNotSameAsA1()
'David McRitchie, 2004-04-30 programming -- Hide row/col on font color
'Hide row/column if font does not match cell A1,
' will also hide rows where cell in column A is empty, or
' columns where Row 1 cell is empty because font is not matched.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim checkfont As Double
Dim cell As Range
checkfont = Cells(1, 1).Font.ColorIndex
For Each cell In Intersect(Columns("A"), ActiveSheet.UsedRange)
If cell.Font.ColorIndex < checkfont Then
cell.EntireRow.Hidden = True
End If
Next cell
For Each cell In Intersect(Rows("1"), ActiveSheet.UsedRange)
If cell.Font.ColorIndex < checkfont Then
cell.EntireColumn.Hidden = True
End If
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

To undo use the same ShowDetail in Debra's original reply.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Chris Sites" wrote ...
That would work, but since my detail data is a different font color than my
basic data, would it be possible to simply use the font color as an
indicator instead of an additional column? What would the HideDetail macro
look like in that case? Thanks.