+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    03-06-2012
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    48

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

    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
    Last edited by CDNcameron; 04-05-2012 at 04:16 PM. Reason: Solved

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

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

    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.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    03-06-2012
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    48

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

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-06-2012
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    48

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

    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

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1