+ Reply to Thread
Results 1 to 6 of 6

Inserting an empty row to divide data randomly

  1. #1
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Inserting an empty row to divide data randomly

    Hi Guys,

    I have a worksheet that has over 20,000 rows of data. For a specific data analysis, I need to randomly divide the data in Sheet1, as per attached file, to chunks of rows. Each chunk of data will have a number of rows between 4 and 40, as per Sheet2 as an example.

    So data rows are needed to be RANDOMLY divided to chunks of any number between 4 and 40 rows by inserting an empty row.

    Can I get your invaluable input with this matter?

    Many thanks in advance.
    Attached Files Attached Files

  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: Inserting an empty row to divide data randomly

    This should do it:
    Please Login or Register  to view this content.
    _________________
    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 Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Inserting an empty row to divide data randomly

    Excellently done. Magnificent code JBeaucaire that ran smoothly and achieved the targeted task. Thanks very much for your precious input with this matter. All the best.

  4. #4
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Inserting an empty row to divide data randomly

    Hi JBeaucaire,

    After using your magnificent code, I was wondering if it could be modified to highlight the line of data under the inserted empty line, with any color? The highlighted line of data is considered the top line of the randomized chunk of data. Having it highlighted will facilitate the entire operation of data analysis.
    Highly appreciating your invaluable expert assistance.
    Thank you.

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

    Re: Inserting an empty row to divide data randomly

    Maybe:
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    04-04-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    204

    Re: Inserting an empty row to divide data randomly

    This is fantastic JBeaucaire, really fantastic. In fact, words of thanking, whatever they would be, wouldn't express my gratitude. Though, I still have to say: Thank you very much. : )

+ 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. Divide a value randomly
    By Pjero in forum Excel General
    Replies: 6
    Last Post: 11-24-2020, 05:10 AM
  2. Inserting an empty row above every highlighted row of data
    By terryhenderson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-12-2014, 02:41 AM
  3. Datalabel.Text randomly returns empty strings in a recursive function
    By Miragel in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-17-2012, 06:17 PM
  4. [SOLVED] Inserting words from a (predefined) set, randomly
    By NathanC in forum Excel General
    Replies: 5
    Last Post: 09-07-2012, 04:44 PM
  5. Excel Randomly Inserting Zeros
    By swfisher in forum Excel General
    Replies: 3
    Last Post: 01-13-2010, 07:51 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