vba for hiding certain rows

[mpb1]

Good evening All,

I am looking for some simple vba to hide rows that fit a certain criteria:

I have a results worksheet (entirely formula driven from data in another worksheet), a simplified version as follows,

A B C D (This column D is the sum of A,B &C)
1 Header Header Header Header
2 1 2 5 8
3 2 2 2 6
4 0 0 0 0
5 0 0 0 0
6 5 5 5 5

This forms the basis of a printed report, but I wish to Hide all the rows that have 0 in column D.

I hope I have expained this adequately
Any help would be most appreciated as always.
Cheers
Mathew

 

[Ken Wright]

Just use Autofilter. Select all the data in your column D and do Data / Filter / Autofilter, then
just hist the dropdwon button, select custom and choose not equal to 0.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



Good evening All,

I am looking for some simple vba to hide rows that fit a certain criteria:

I have a results worksheet (entirely formula driven from data in another worksheet), a simplified
version as follows,

A B C D (This column D is the sum of A,B
&C)
1 Header Header Header Header
2 1 2 5 8
3 2 2 2 6
4 0 0 0 0
5 0 0 0 0
6 5 5 5 5

This forms the basis of a printed report, but I wish to Hide all the rows that have 0 in column D.

I hope I have expained this adequately
Any help would be most appreciated as always.
Cheers
Mathew

 

[Ron de Bruin]

One way

Filter the data using Autofilter and choose custom in the D Column and set "does not equal 0".
Put on your macro recorder when doing this and you have a start with your code.

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




Good evening All,

I am looking for some simple vba to hide rows that fit a certain criteria:

I have a results worksheet (entirely formula driven from data in another worksheet), a simplified version as follows,

A B C D (This column D is the sum of A,B &C)
1 Header Header Header Header
2 1 2 5 8
3 2 2 2 6
4 0 0 0 0
5 0 0 0 0
6 5 5 5 5

This forms the basis of a printed report, but I wish to Hide all the rows that have 0 in column D.

I hope I have expained this adequately
Any help would be most appreciated as always.
Cheers
Mathew

 

[mpb1]

Hi Ken.
Cheers,
However, I have 0's in column D further down the worksheet, relating to
further data analysis, and I need to keep these. I was looking for some code
so I could define the range in column D.
I should have probably mentioned this in my original post!
Thank you very much for your thoughts,
Cheers
Mathew

 

[Ken Wright]

So why not just define the range in the autofilter when you set it up. As long as you have a gap
of at least 1 empty row between the two sets of data, you should be OK as long as you just select
the first set when you set up the autofilter.

 

[mpb1]

Ken,
You've been reading my mind. Am trying this way now, seems do-able!
Cheers again
mathew

 

[GB]

Good evening All,

I am looking for some simple vba to hide rows that fit a certain criteria:

I have a results worksheet (entirely formula driven from data in another
worksheet), a simplified version as follows,

A B C D (This column
D is the sum of A,B &C)
1 Header Header Header Header
2 1 2 5 8
3 2 2 2 6
4 0 0 0 0
5 0 0 0 0
6 5 5 5 5

This forms the basis of a printed report, but I wish to Hide all the rows
that have 0 in column D.

I hope I have expained this adequately
Any help would be most appreciated as always.
Cheers
Mathew

=================================================

The following VBA script does the job, if you prefer a VBA solution:

For i = 2 To 30
If Cells(i, 4).Value = 0 Then Rows(i).Hidden = True
Next i

Change 2 and 30 to the start and end rows you want to consider hiding.
4 Corresponds to Column D, and may need changing

Someone else may come up with a better piece of code, as I am just a
beginner. Please don't blame me if it does not work!

Geoff