Conditional Hiding of Rows or Columns

[sanjay_suda]

Hello Good People,

I have a very large spreadsheet with approx 35 columns and over 50
rows. However, I do not need to see all the rows at any point in tim
and may want to look at just a limited number of rows. Generally I nee
to look at only those rows that have a non zero value in a specifi
column. Such rows can be randomly dispersed within the spreadsheet.

I can use the option of sorting the full spreadsheet based upon th
value of the cells in the particular column, however, this does no
help very much as I would need to do this every time I referred to th
sheet - which may be upto 20 times a day (the sheet is populate
automatically from a database every 15 minutes).

If possible I would like the rows to hide and unhide themselve
automatically based upon a condition like If value in a specifie
column is non zero then hide the row. This will also allow me to retai
the original sort order of the rows which is based upon differen
columns.

Is this possible.

Regards and Thanks for Assistanc

 

[JE McGimpsey]

You could use an Event Macro to automatically hide and unhide rows, but
it sounds to me like Data/Sort/AutoSort will work for you. Choose Custom
Filter, column does not equal 0.

 

[JE McGimpsey]

Replace went the wrong way <sigh>. Try:

Data/Filter/AutoFilter

 

[Peo Sjoblom]

I thought for a moment it was some new Mac Excel function <g>

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[JE McGimpsey]

I thought for a moment it was some new Mac Excel function <g>

Nope - 'twas a probably-shouldn't-have-had-that-last-beer function.

 

[opeel]

Select the entire sheet using the sqaure button in the top left of th
sheet (between the header A and the row header 1). Then from the dat
menu select autofilter. Use the drop down box on the column with th
blank cells you wish to filter out. Select the (Non Blanks) option

 

[Guest]

I have a similar problem but instead of being an entire column I'm trying to hide the zero values in a range of cells. I've been trying to get the below macro to work but it's just not happening for me. Can anyone suggest the necessary changes? The Macro is to hide zero values in column 6, rows 13 to 70.

Sub hide_rows()

For rwIndex = 13 To 70
For colIndex = 6 To 6
With Worksheets("UCS").Cells(rwIndex, colIndex)
If .Value = 0 Then EntireRow.Hidden = True
End With
Next colIndex
Next rwIndex
End Sub

Thanks for any advice you can give.

AMcB