+ Reply to Thread
Results 1 to 7 of 7

Insert blank rows above a table based on unique values in one of the columns in the table

  1. #1
    Forum Contributor
    Join Date
    10-03-2015
    Location
    North Carolina, USA
    MS-Off Ver
    Office 365
    Posts
    167

    Insert blank rows above a table based on unique values in one of the columns in the table

    Hi,

    First, my apologies if anything I write is unclear or if I have not provided sufficient information. I almost never have the opportunity to work with vba, so when I do get to play around with it one every year or two, I come at it as an absolute newb.

    My question (with some details):

    I copied some vba from a previous macro that splits data from a table in one sheet onto several sheets based on contents in cells of one of the columns in the original sheet. In other words, if there are three unique strings in column E, then my macro creates three new sheets with names that match the unique strings in column E in the original table, and then it copies rows of data from the original table that contain each unique string, and pastes those rows into the corresponding new sheet. This code is working perfectly (even thought I don't understand how it all works).

    What I want to happen next is, on each of the newly created sheets, I want to count the number of unique strings in column B, then insert that number of blank rows + 5 at the top of the sheet. In other words, if there are 5 unique strings in column B, then I want to insert 10 blank rows at the top of the sheet. I don't know if it matters, but the new sheets will always start at the 4th sheet (first three sheets are fixed, and will always be present). Possible names of the new sheets are, "ICO RWE", "ICO RWE-RSH", "ICO RSH", "ICR SSU", and "ICR MED", but not all sheets will always be present. For example, one workbook might only have "ICO RWE" and "ICO RSH".

    Any and all help is greatly appreciated. Please let me know if more details are needed.

    Thank you!
    Carlos





    Edit: This is to inform of cross-posting. I tried to do this yesterday, but apparently it didn't stick. Anyway, I just wanted to inform everyone here that I cross-posted this question at Chandoo. I would provide a link, but I haven't posted enough here to have that privilege. I cross-posted because I wasn't getting an answer to my question, and in the past, I would receive several answers almost immediately. Sorry. I would edit my post on the other site to ask a different question, but apparently posts there are not editable.
    Last edited by carlito2002wgn; 02-08-2019 at 10:59 AM.

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Insert blank rows above a table based on unique values in one of the columns in the ta

    Welcome to the forum
    - test on a COPY of your workbook!

    1. code assumes that headers are in row 1
    2. value in B1 is excluded from count
    3. function UniqueCount ignores empty cells
    4. can call InsertRows at the end of your original procedure like this ...
    Please Login or Register  to view this content.
    Put this code in a new standard module
    Please Login or Register  to view this content.

    The function can also be used like any other Excel formula
    =UniqueCount(B2:B90) counts unique values in range B2:B90
    Last edited by kev_; 02-07-2019 at 01:56 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Contributor
    Join Date
    10-03-2015
    Location
    North Carolina, USA
    MS-Off Ver
    Office 365
    Posts
    167

    Re: Insert blank rows above a table based on unique values in one of the columns in the ta

    Kev_, thank you so much for your time and effort! This is all like some sort of magical wizardry to me I fear I lack the brain power to ever be able to write my own code like you. Or, maybe it isn't a lack of brain power, but rather, I just have a different kind of brain!

    I thought all sub procedures had to end with "End Sub", but now I see there is something called a "function", and those can go after "End Sub". Ha!

    So, if I wanted to add more vba, is my only option to do so in a new module? For example, my next goal is to figure out how to take the unique strings from column B (this time, including the header), and have them pasted above the table that we just moved down. In other words, the header from column B would be pasted in B3, and all of the unique strings pasted below it.

    Anyway, once I figure out how to do that, is there a way that I can add that code so that it runs after the code that you provided above?

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Insert blank rows above a table based on unique values in one of the columns in the ta

    Anyway, once I figure out how to do that, is there a way that I can add that code so that it runs after the code that you provided above?
    Can add more precudures in same module (or in another general module)
    Please Login or Register  to view this content.

    and then call like this
    Please Login or Register  to view this content.
    Last edited by kev_; 02-08-2019 at 11:31 AM.

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Insert blank rows above a table based on unique values in one of the columns in the ta

    For example, my next goal is to figure out how to take the unique strings from column B (this time, including the header), and have them pasted above the table that we just moved down. In other words, the header from column B would be pasted in B3, and all of the unique strings pasted below it.
    - is a new subject
    - and therefore it's time for you to start a new thread

  6. #6
    Forum Contributor
    Join Date
    10-03-2015
    Location
    North Carolina, USA
    MS-Off Ver
    Office 365
    Posts
    167

    Re: Insert blank rows above a table based on unique values in one of the columns in the ta

    Ok, I'll play around and see what happens. Thank you!!

  7. #7
    Forum Contributor
    Join Date
    10-03-2015
    Location
    North Carolina, USA
    MS-Off Ver
    Office 365
    Posts
    167

    Re: Insert blank rows above a table based on unique values in one of the columns in the ta

    Quote Originally Posted by kev_ View Post
    - is a new subject
    - and therefore it's time for you to start a new thread
    Ha, no problem

    I'll see what I come up with, and if I can't make it work, I'll be back to start a new thread.
    Last edited by carlito2002wgn; 02-08-2019 at 01:25 PM.

+ 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. Table that insert measures and insert blank rows
    By searme in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2016, 04:49 AM
  2. [SOLVED] Get values from varying rows and columns of Pivot Table and enter those in different table
    By Richavlaues in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-25-2016, 10:24 AM
  3. Finding a macro that will populate a table from another table based on a columns values
    By Daril_Ghiroza in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2015, 02:26 PM
  4. create a table based on values selected in rows and columns
    By hemagala in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2015, 03:46 AM
  5. [SOLVED] Insert Multiple Blank Rows to a ListObject Table
    By Dominicus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2012, 04:14 AM
  6. Replies: 6
    Last Post: 10-18-2011, 07:34 PM
  7. insert blank rows based on columns a values numerically
    By elkhole in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-18-2009, 04:57 AM

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