+ Reply to Thread
Results 1 to 7 of 7

Macro to auto fill rows based on one cell value

  1. #1
    Registered User
    Join Date
    03-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    20

    Macro to auto fill rows based on one cell value

    Hi,

    I am trying to create a macro to insert and fill in whole rows based on where one cell has a value in it. I have a huge spreadsheet and need to create a summary one based on the items selected on it. So where a row is filled in with a number of items, it should appear on the other sheet, but not those where the number of items is blank. There is header/footer info so I don't want lots of blank lines which is why I thought a macro would be better than a formula, but I'm hoping someone with more expertise than me could advise!

    Example attached.

    Thanks (again)
    Attached Files Attached Files
    Last edited by jennieS; 08-18-2010 at 03:10 PM.

  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: Macro to auto fill rows based on one cell value

    No VBA needed. Add a simple "key" column to your first sheet (and hide it if you wish), then normal worksheet formulas can construct your table in realtime.

    There are two formula, colored to show which sections has each.
    Attached Files Attached Files
    _________________
    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
    Registered User
    Join Date
    03-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Macro to auto fill rows based on one cell value

    Quote Originally Posted by JBeaucaire View Post
    No VBA needed. Add a simple "key" column to your first sheet (and hide it if you wish), then normal worksheet formulas can construct your table in realtime.

    There are two formula, colored to show which sections has each.

    Thank you so much, I've just had a play with it and it works brilliantly!

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

    Re: Macro to auto fill rows based on one cell value

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  5. #5
    Registered User
    Join Date
    03-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Macro to auto fill rows based on one cell value

    Quote Originally Posted by JBeaucaire View Post
    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
    Hi,
    Sorry, I didn't want to mark it solved until I actually got round to using it in my spreadsheet, which I've done today. It does work, however I need to be able to separate it into sections, as per the attached updated example. How can I get this to work please?

    Thanks again,
    Jennie
    Attached Files Attached Files

  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: Macro to auto fill rows based on one cell value

    Like so...it's pretty much the exact same thing, but instead of indexing all of column A, we index "sections".
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-19-2009
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Macro to auto fill rows based on one cell value

    Quote Originally Posted by JBeaucaire View Post
    Like so...it's pretty much the exact same thing, but instead of indexing all of column A, we index "sections".
    Got it, thank you again

+ 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