Is it possible to create a macro that repeats for the cell below till reaching a blank cel

[CDNcameron]

I've been tasked with setting up several macros that will a) filter a value on the table, than b)print the table.

I know this would be possible by individual setting it to filter to A10, than print, than A11, than print and so on but the lists will range from 50 to over 100 so I'm wondering if it's possible to generate a macro that rather than being the same thing 8 pages long with each different filter cell typed in, generate one that starts at a row and repeats moving down one cell until reaching a blank.

What do you think, possible? or do I have a lot of copying of one macro for each row ahead of me?

Thanks for any help
~James H. Cameron

[JBeaucaire]

It's not hard to create a loop to repeat actions, but your need isn't actually clear. Perhaps a sample workbook will help you explain it more clearly?

What does "filter to A10" even mean? Are you talking about filtering a table of data by all the unique values in a single column and printing each result? Again, a sample workbook would mean our responses would be exactly useful.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[royUK]

Why would you want to loop through cells? Iit's not at all clear what you need. There's a feature in Excel called AutoFilter.

[CDNcameron]

Sorry for not being clearer. A sample sheet is attached and the Macro set up on the first page does what I need for the first two numbers. I know how to set a macro up to do an auto filter for the value in a specified cell, and than to have it print said cell. the quick one I've set up that only does the first two I did by copying it and just changing the specified cell for the second.

Where my question comes in. In order to set up a macro that will print off a separate sheet for each filtered number in the list, will I need to continue copying the same macro down and changing the specified cell. OR is it possible to set it up to loop, and have each it automatically move the specified cell to the one below the previous cell. ie first cell to be filtered and printed is D10, than it will filter and print D11, and so on until reaching a blank cell.

Hope this is clearer than the last post. English might be my first language but accounting is my major haha.

Thanks in advance for having a look at this

~James H. Cameron

[CDNcameron]

Hey, managed to educate myself on Loops and just wanted to tank you both for mentioning them as it was not something I'd heard about before and it allowed be to get this set up. The code I set up is as follows;

Please Login or Register  to view this content.

Thanks again for mentioning Loops, can't believe that with two "Business Computer" courses in my program, and roughly 4 months dedicated to teaching us Excel, macros were never even mentioned.

Thanks a 3rd time.
~James H. Cameron

[JBeaucaire]

Some quick tips... you want to eliminate all the "selecting" and "activating" where you can. Your macro recorder will show you a lot of that, just restructure the recorded code to send your commands to the correct sheets/objects/ranges directly. Also, you should declare every variable type to save yourself hours of headaches later trying to debug problem code. By turning on the Auto Syntax Check (which puts Option Explicit at the top of new modules), Excel will help you by pointing out many errors as you make them....very helpful. So I declared your "i" variable.

So your original macro could possibly be:

Please Login or Register  to view this content.

Here's another way to loop through the values on one sheet to filter/print another without really ever activating either:

Please Login or Register  to view this content.