vba for hiding certain rows

  • Thread starter Thread starter mpb1
  • Start date Start date
M

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
 
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
 
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
 
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
 
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.
 
Ken,
You've been reading my mind. Am trying this way now, seems do-able!
Cheers again
mathew
 
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
 
Back
Top