I'm currently using Excel 2002 SP3. I use conditional formatting in many of my spreadsheets, but I haven't been able to figure out how to "Hide" a row or column based on a certain condition. Is there a method for doing this already built into Excel's functionality, and if not, is there a way to do it with VBA? Any help would be greatly appreciated! Thank you.

An example of hiding a row based on a value in any formula cell in Column A

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
If cell.text = "" Or cell.Value = 0 Then cell.EntireRow.Hidden = True
Next cell
End With
End Sub

This is event code.

Right-click on the sheet tab and copy/paste into that module.


Gord Dibben MS Excel MVP

On Wed, 27 Sep 2006 00:15:34 +0100, sczegus
wrote:


I'm currently using Excel 2002 SP3. I use conditional formatting in many
of my spreadsheets, but I haven't been able to figure out how to "Hide"
a row or column based on a certain condition. Is there a method for
doing this already built into Excel's functionality, and if not, is
there a way to do it with VBA? Any help would be greatly appreciated!
Thank you.

Thank you for the suggestion! To clarify my situation, I'm working with an attendance form that has over 45 rows to accomodate different activities, and 16 columns to accomodate each day in the timeframe (1st to the 15th, or 16th to the end of the month). I want the attendance form to be as uncluttered and user friendly as possible, so I'm looking for a way to automatically hide the rows (row height=0) that aren't being used, and to automatically hide the columns (column width=0) that aren't necessary for the current timeframe (i.e. the last 3 columns when the timeframe is February 16th to February 28th).

For simplicity sake, let's say the range is rows 1-10, and I'm trying to individually hide rows where the cell in the A column contains the word "HIDE".

Gord, the VBA that you've listed below ... does this run automatically, or do you have to "call" the routine by attaching it to a button or something of that nature?

Thank you in advance for any help you can provide!

The code runs when you change selected cell.

Option Compare Text
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In Range("A1:A10")
If cell.Value = "HIDE" Then _
cell.EntireRow.Hidden = True
Next cell
End With
End Sub


Gord

On Wed, 27 Sep 2006 16:03:41 +0100, sczegus
wrote:


Thank you for the suggestion! To clarify my situation, I'm working with
an attendance form that has over 45 rows to accomodate different
activities, and 16 columns to accomodate each day in the timeframe (1st
to the 15th, or 16th to the end of the month). I want the attendance
form to be as uncluttered and user friendly as possible, so I'm looking
for a way to _automatically_ hide the rows (row height=0) that aren't
being used, and to _automatically_ hide the columns (column width=0)
that aren't necessary for the current timeframe (i.e. the last 3
columns when the timeframe is February 16th to February 28th).

For simplicity sake, let's say the range is rows 1-10, and I'm trying
to individually hide rows where the cell in the A column contains the
word "HIDE".

Gord, the VBA that you've listed below ... does this run automatically,
or do you have to "call" the routine by attaching it to a button or
something of that nature?

Thank you in advance for any help you can provide!

Gord Dibben Wrote:
An example of hiding a row based on a value in any formula cell in
Column A

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each cell In .Columns(1).SpecialCells(xlCellTypeFormulas)
If cell.text = "" Or cell.Value = 0 Then cell.EntireRow.Hidden
= True
Next cell
End With
End Sub

This is event code.

Right-click on the sheet tab and copy/paste into that module.


Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP