+ Reply to Thread
Results 1 to 3 of 3

VBA to Insert Formatted Rows Based On Cell Reference

  1. #1
    Registered User
    Join Date
    07-25-2019
    Location
    San Diego, CA
    MS-Off Ver
    16.0.11727.20222
    Posts
    34

    VBA to Insert Formatted Rows Based On Cell Reference

    Hi all!

    I'm making a template that requires variable rows in certain sections. I had initially started with simple inserts based on the row, but didn't think about how the row referenced would change based on inserted rows above it.

    I then tried to tailor it based on what I found here: https://www.extendoffice.com/documen...ell-value.html but seem to be missing something. Since the sections where rows would need to be added have headers, I think I can use them as a reference to add rows below it with each button press, but I'm having a hard time figuring out how.

    I've attached a copy of the file to make things more clear. I would have to add rows under "LABEL CLAIM," upwards upwards of 50 rows, as well as rows under "ACTIVE INGREDIENTS WITH A CONCENTRATION CLAIM ON LABEL", under "ACTIVE INGREDIENTS THAT DO NOT HAVE A CONCENTRATION CLAIM: PROPRIETARY BLEND" and under "EXCIPIENTS". So I think if I reference the cells that contain that text, I should be able to insert rows beneath them, right?

    Thank you for your help!

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: VBA to Insert Formatted Rows Based On Cell Reference

    The natural tendency is to start at the top and work our way down on a sheet. But when adding, inserting or deleting rows, it is better to start with the bottom most target and work our way upwards. So if you have headers at rows 30, 41, 53 and 62 where you need to insert rows, then you would want to start your insertion using row 62 as the reference. But you want to retain the data in that row so you use Offset(1) to make the insertion below that row. The other row numbers will not change as a result of the insertions made under row 62, when you are finished with 62 move up to 53 and offset to make the insertions, same with 41 and 30.

    I am not sure how your are making the insertions (manually or by vba or other) so I won't offer any suggestions along that line. But the logic should help you along.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Registered User
    Join Date
    07-25-2019
    Location
    San Diego, CA
    MS-Off Ver
    16.0.11727.20222
    Posts
    34

    Re: VBA to Insert Formatted Rows Based On Cell Reference

    I'm sure that there's a simpler way to do this, but this is the code that I used:

    HTML Code: 
    I ended up using the code for 4 different buttons, each one referencing a different cell with a different value. I'm sure I can loop it, but I'm not really sure how, especially since I need to retain formatting of the cells, so the code changes slightly depending on where the insert is taking place. I just wanted to share here so I could mark the thread as solved, and show my appreciation to JLGWhiz for their insight.

    Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Insert formatted rows + a copy of istelf
    By lhunt0194 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2020, 08:55 PM
  2. VBA Insert formatted rows + a copy of itself
    By lhunt0194 in forum Excel General
    Replies: 2
    Last Post: 10-26-2020, 08:52 PM
  3. Need multiple macro buttons to insert new, formatted rows
    By msmetal in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-09-2014, 12:05 PM
  4. [SOLVED] Insert a value based on row reference in another cell...
    By BrownBoy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2014, 03:24 PM
  5. Align and insert rows based on reference column
    By clarkd6147 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-27-2012, 04:37 PM
  6. Insert rows via values of formatted cell
    By christya24 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2009, 10:52 AM
  7. Macro to insert formatted rows
    By oneblueaugust in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-24-2009, 05:05 PM

Tags for this Thread

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