Create A Looped If Structure

**nevi** · 02-27-2011, 01:57 PM

Hey There!

I think what I am trying to do here is fairly simple, but for the life of me I can not remember how to write a Loop statement. I'm pretty basic when it comes to VB and Macros and am trying to teach myself as I go...

In a nut shell, I need a macro that will follow this logic:
* Add the values in K22 and M22 together
* If this value is 0, hide the row
* If it is not 0, move on to the next row

If needs to loop through this logic, checking every row from 22 to 1021, hiding all the rows where the value is 0 and leaving the rows that are not 0 displayed.

I came up with this:

Please Login or Register  to view this content.

...
so on and so forth...

I don't particularily want to type this out 1,000 times though, and I seem to remeber that a Loop structure could do this for me, but I just don't recall how to do it.

Thanx in adance for your help!!

**davesexcel** · 02-27-2011, 02:23 PM

Try this

Please Login or Register  to view this content.

**nevi** · 02-27-2011, 02:37 PM

Worked like a charm, except that it left the last two rows for some reason. Also, does it take into account if the value in K is 0 but there is a value in M? I don;t see any references to M there, so I am worried that it does not...

Thank!!

I have another thing I'd like to do with this book (then I promise I will stop perstering

). I have no idea if this is even possible...

Basically, the form this is creating will eventually be printed out, so I need to make it visually appealing as well. I will set up a seperate Command Button to print with (rather than using the normal CTRL-P), but it there a Macro that can tell it to count how many rows there are in (K22:K1021)+(M22:M1021), then start a new page for ever 35 rows, with rows 1 though 21 repeated at the top of each page?

**nevi** · 02-27-2011, 02:39 PM

Figured out the whol K and M thing. Used column O to add them up, then just replaced all the K's in the script you gave me with an O. Works great - thanx a million!!

:D

**davesexcel** · 02-27-2011, 03:23 PM

The code adds the two together, if they equal 0 then the row is hidden.

You could even use the autofilter to hide the rows that have zeros.

**nevi** · 02-27-2011, 03:40 PM

Not that I am an expert myself, but the people that will be using this form are some of the most computer illeterate I have ever encoutered... Asking them to use the Filters would be too much...

I even had to put a radio button in here that with one click resaves the file with the current date and time, because getting to click Save As and change the file name was too complicated... They keep overwritting the historical data...

LMAO