Hide rows with zero value?

[Steven]

just wondering how I would be able to hide rows on sheets if some cells are
blank? the cells contain formulas they are only blank because `zero
values` in >tools>options>view is unchecked.

I have a sheet set up to SUMIF values from about 16 other sheets, and it
returns the values depending on 72 corresponding codes, I would like to hide
any of the 72 rows of data that are blank.

the values returned will change on a week to week and daily basis, so I
would like to be able to use the same design for each new week, hope that
makes sense.

 

[Frank Kabel]

Hi
try something like the following:
Sub hide_rows()
Dim RowNdx As Long
Dim LastRow As Long

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).hidden = True
End If
Next RowNdx
End Sub

hides all blank rows in column A

 

[Dave Hawley]

Hi Steven

A good way to do this is via Data>Filter>Auto Filter. Hide the un-needed
rows, then go to View>Custom Views and "Add" the View with a name like
"HideZero". Next time you need it simply show the View or Record a macro
showing it. The good thing is you don't need Auto Filters applied before
showing the saved View.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Hawley]

.....or, if Colulmn "A" has the cells to check for Zero, use some code
like;


Sub HideZeros()
Dim rRange As Range
For Each rRange In Range("A1", Range("A65536").End(xlUp))
rRange.EntireRow.Hidden = _
(rRange = 0 And Len(rRange) = 1)
Next rRange
End Sub

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Steven]

Dave when i high light the rows and go to data filter, autofilter is not an
option i can use.

i need something that will say look at colum I, (I contains totals of rows
B3-H3 down to B71-H71) if any of the cells from I3-I71 have a zero value,
hide row 3,4,5,6,,,, 71 etc, hope thats as clear as mud

 

[Ken Wright]

So select just Col I (Or the cell with your header in, down to the last cell of
data), do Data / Filter / Autofilter and then Filter on 'Not Equal to 0'

 

[Dave Peterson]

A couple of reasons that Data|filter|Autofilter isn't available:

1. Your worksheet is protected
(tools|Protection|unprotect worksheet to check. You may need a password.)

2. Your worksheet is grouped with another worksheet.
Check the title bar. Do you see something like "book1.xls [Group]"
If yes, right click on a worksheet tab and select "Ungroup Sheets"

 

[Steven]

Its not protected or grouped

A couple of reasons that Data|filter|Autofilter isn't available:

1. Your worksheet is protected
(tools|Protection|unprotect worksheet to check. You may need a password.)

2. Your worksheet is grouped with another worksheet.
Check the title bar. Do you see something like "book1.xls [Group]"
If yes, right click on a worksheet tab and select "Ungroup Sheets"

Dave when i high light the rows and go to data filter, autofilter is not an
option i can use.

i need something that will say look at colum I, (I contains totals of rows
B3-H3 down to B71-H71) if any of the cells from I3-I71 have a zero value,
hide row 3,4,5,6,,,, 71 etc, hope thats as clear as mud

cells
are to
hide

 

[Ken Wright]

Either we are all talking by each other here, or something is screwy somewhere.
This is something that would usually be a 2 second job to most of the regulars,
so I know we're not all losing it. Are you able to send me the file at all so I
can take a look first hand, only we've pretty much covered most things it could
be??

ken.wright [at] ntlworld.com

 

[Dave Peterson]

What happens when you try it?

Is it just disabled or does something else happen (error message)?

If you try it against a test worksheet in a new workbook, does it work?

Its not protected or grouped

A couple of reasons that Data|filter|Autofilter isn't available:

1. Your worksheet is protected
(tools|Protection|unprotect worksheet to check. You may need a password.)

2. Your worksheet is grouped with another worksheet.
Check the title bar. Do you see something like "book1.xls [Group]"
If yes, right click on a worksheet tab and select "Ungroup Sheets"

Dave when i high light the rows and go to data filter, autofilter is not an
option i can use.

i need something that will say look at colum I, (I contains totals of rows
B3-H3 down to B71-H71) if any of the cells from I3-I71 have a zero value,
hide row 3,4,5,6,,,, 71 etc, hope thats as clear as mud

just wondering how I would be able to hide rows on sheets if some cells
are
blank? the cells contain formulas they are only blank because `zero
values` in >tools>options>view is unchecked.

I have a sheet set up to SUMIF values from about 16 other sheets, and it
returns the values depending on 72 corresponding codes, I would like to
hide
any of the 72 rows of data that are blank.

the values returned will change on a week to week and daily basis, so I
would like to be able to use the same design for each new week, hope that
makes sense.

 

[Ken Wright]

Dave

Not Shared
Not Grouped
Sheet not Protected
Workbook not protected
Track changes not enabled
Autofilter greyed out, but Advanced Filter available as an option - Same on
every part of the worksheet(s)/book I tried
All Tools / Trace Options greyed out
Putting Autofilter on another sheet in a new book and moving the sheet to this
book results in loss of the Autofilter, BUT, Data / Filter / Autofilter is
checked albeit greyed out??

??

 

[Ken Wright]

And if I move the test sheet I put into the book back out of the book where it
came from, the autofilter is reactivated??

 

[Frank Kabel]

Hi Ken
this sounds as if the workbook is somehow corrupted (very interesting).

 

[Debra Dalgleish]

One more thing to try --
Choose Tools>Options

Its not protected or grouped

A couple of reasons that Data|filter|Autofilter isn't available:

1. Your worksheet is protected
(tools|Protection|unprotect worksheet to check. You may need a
password.)

2. Your worksheet is grouped with another worksheet.
Check the title bar. Do you see something like "book1.xls [Group]"
If yes, right click on a worksheet tab and select "Ungroup Sheets"

Dave when i high light the rows and go to data filter, autofilter is not

an

option i can use.

i need something that will say look at colum I, (I contains totals of

rows

B3-H3 down to B71-H71) if any of the cells from I3-I71 have a zero

value,

hide row 3,4,5,6,,,, 71 etc, hope thats as clear as mud


just wondering how I would be able to hide rows on sheets if some

cells

are

blank? the cells contain formulas they are only blank because `zero
values` in >tools>options>view is unchecked.

I have a sheet set up to SUMIF values from about 16 other sheets, and

it

returns the values depending on 72 corresponding codes, I would like

to

hide

any of the 72 rows of data that are blank.

the values returned will change on a week to week and daily basis, so

I

would like to be able to use the same design for each new week, hope

that

makes sense.

 

[Dave Peterson]

Oooooh. I like this one!

One more thing to try --
Choose Tools>Options

Its not protected or grouped

A couple of reasons that Data|filter|Autofilter isn't available:

1. Your worksheet is protected
(tools|Protection|unprotect worksheet to check. You may need a
password.)

2. Your worksheet is grouped with another worksheet.
Check the title bar. Do you see something like "book1.xls [Group]"
If yes, right click on a worksheet tab and select "Ungroup Sheets"



Steven wrote:

Dave when i high light the rows and go to data filter, autofilter is not
an

option i can use.

i need something that will say look at colum I, (I contains totals of
rows

B3-H3 down to B71-H71) if any of the cells from I3-I71 have a zero
value,

hide row 3,4,5,6,,,, 71 etc, hope thats as clear as mud


just wondering how I would be able to hide rows on sheets if some
cells

are

blank? the cells contain formulas they are only blank because `zero
values` in >tools>options>view is unchecked.

I have a sheet set up to SUMIF values from about 16 other sheets, and
it

returns the values depending on 72 corresponding codes, I would like
to

hide

any of the 72 rows of data that are blank.

the values returned will change on a week to week and daily basis, so
I

would like to be able to use the same design for each new week, hope
that

makes sense.

 

[Ken Wright]

Deb, YOU ARE JUST TOO DAMNED GOOD AT THIS!!!!!!!!!!!!!!!!! <vbg>

That fixed it

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

One more thing to try --
Choose Tools>Options

Its not protected or grouped

A couple of reasons that Data|filter|Autofilter isn't available:

1. Your worksheet is protected
(tools|Protection|unprotect worksheet to check. You may need a
password.)

2. Your worksheet is grouped with another worksheet.
Check the title bar. Do you see something like "book1.xls [Group]"
If yes, right click on a worksheet tab and select "Ungroup Sheets"



Steven wrote:

Dave when i high light the rows and go to data filter, autofilter is not
an

option i can use.

i need something that will say look at colum I, (I contains totals of
rows

B3-H3 down to B71-H71) if any of the cells from I3-I71 have a zero
value,

hide row 3,4,5,6,,,, 71 etc, hope thats as clear as mud


just wondering how I would be able to hide rows on sheets if some
cells

are

blank? the cells contain formulas they are only blank because `zero
values` in >tools>options>view is unchecked.

I have a sheet set up to SUMIF values from about 16 other sheets, and
it

returns the values depending on 72 corresponding codes, I would like
to

hide

any of the 72 rows of data that are blank.

the values returned will change on a week to week and daily basis, so
I

would like to be able to use the same design for each new week, hope
that

makes sense.

 

[Steven]

Thanks everyone for your help!

xxx

One more thing to try --
Choose Tools>Options

Its not protected or grouped

A couple of reasons that Data|filter|Autofilter isn't available:

1. Your worksheet is protected
(tools|Protection|unprotect worksheet to check. You may need a
password.)

2. Your worksheet is grouped with another worksheet.
Check the title bar. Do you see something like "book1.xls [Group]"
If yes, right click on a worksheet tab and select "Ungroup Sheets"



Steven wrote:

Dave when i high light the rows and go to data filter, autofilter is not
an

option i can use.

i need something that will say look at colum I, (I contains totals of
rows

B3-H3 down to B71-H71) if any of the cells from I3-I71 have a zero
value,

hide row 3,4,5,6,,,, 71 etc, hope thats as clear as mud


just wondering how I would be able to hide rows on sheets if some
cells

are

blank? the cells contain formulas they are only blank because `zero
values` in >tools>options>view is unchecked.

I have a sheet set up to SUMIF values from about 16 other sheets, and
it

returns the values depending on 72 corresponding codes, I would like
to

hide

any of the 72 rows of data that are blank.

the values returned will change on a week to week and daily basis, so
I

would like to be able to use the same design for each new week, hope
that

makes sense.

 

[Steven]

Debs could you explain what show placeholders does? i do not understand what
this had to do with autofilter not showing?

Thanks

Steve

One more thing to try --
Choose Tools>Options

Its not protected or grouped

A couple of reasons that Data|filter|Autofilter isn't available:

1. Your worksheet is protected
(tools|Protection|unprotect worksheet to check. You may need a
password.)

2. Your worksheet is grouped with another worksheet.
Check the title bar. Do you see something like "book1.xls [Group]"
If yes, right click on a worksheet tab and select "Ungroup Sheets"



Steven wrote:

Dave when i high light the rows and go to data filter, autofilter is not
an

option i can use.

i need something that will say look at colum I, (I contains totals of
rows

B3-H3 down to B71-H71) if any of the cells from I3-I71 have a zero
value,

hide row 3,4,5,6,,,, 71 etc, hope thats as clear as mud


just wondering how I would be able to hide rows on sheets if some
cells

are

blank? the cells contain formulas they are only blank because `zero
values` in >tools>options>view is unchecked.

I have a sheet set up to SUMIF values from about 16 other sheets, and
it

returns the values depending on 72 corresponding codes, I would like
to

hide

any of the 72 rows of data that are blank.

the values returned will change on a week to week and daily basis, so
I

would like to be able to use the same design for each new week, hope
that

makes sense.

 

[Debra Dalgleish]

The arrows in the AutoFilter heading cells are hidden if objects are
hidden. So, if objects are hidden before the AutoFilter is in place,
Excel won't allow you to create an AutoFilter (I'm not sure if that's a
bug or a feature).

If Show Placeholders is selected, some objects, such as charts, are
represented by an outline. Other objects, like AutoFilter arrows, are
visible, so when that option is selected, you can create or use an
AutoFilter.

Debs could you explain what show placeholders does? i do not understand what
this had to do with autofilter not showing?

Thanks

Steve

One more thing to try --
Choose Tools>Options

Its not protected or grouped



A couple of reasons that Data|filter|Autofilter isn't available:

1. Your worksheet is protected
(tools|Protection|unprotect worksheet to check. You may need a

password.)


2. Your worksheet is grouped with another worksheet.
Check the title bar. Do you see something like "book1.xls [Group]"
If yes, right click on a worksheet tab and select "Ungroup Sheets"



Steven wrote:


Dave when i high light the rows and go to data filter, autofilter is

not

an


option i can use.

i need something that will say look at colum I, (I contains totals of

rows


B3-H3 down to B71-H71) if any of the cells from I3-I71 have a zero

value,


hide row 3,4,5,6,,,, 71 etc, hope thats as clear as mud



just wondering how I would be able to hide rows on sheets if some

cells


are


blank? the cells contain formulas they are only blank because `zero
values` in >tools>options>view is unchecked.

I have a sheet set up to SUMIF values from about 16 other sheets, and

it


returns the values depending on 72 corresponding codes, I would like

to


hide


any of the 72 rows of data that are blank.

the values returned will change on a week to week and daily basis, so

I


would like to be able to use the same design for each new week, hope

that


makes sense.