+ Reply to Thread
Results 1 to 5 of 5

Insert blank rows when data changes and calculate how many rows to insert

  1. #1
    Registered User
    Join Date
    10-23-2020
    Location
    Chester
    MS-Off Ver
    365
    Posts
    3

    Question Insert blank rows when data changes and calculate how many rows to insert

    I'm printing some price tags from an excel spreadsheet.
    On the spreadsheet each row has store code, product and the sell price. In column A is a code for a store, in column B is the product and in Column C is the price. My question is this, when the store code changes from 001 to 002 for example, I would like excel to insert blank rows so I can separate each stores products. That's pretty straight forward, but the problem I have is that I'm printing 6 tickets per sheet, and the number of products per store differ so I want excel to look at how many rows there are per store (ie the code in column A), then work out how many blank inserted rows are needed to bring that up to a multiple of 6. That way, no sheet will have two stores tickets on the same sheet.

    For example, if there are 58 rows of products for store code 001, then Excel will know to insert 2 blank rows to bring that upto 60 rows in total. It will mean that the products for store 002 will start on the next printed sheet.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    2,179

    Re: Insert blank rows when data changes and calculate how many rows to insert

    Hi
    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

  3. #3
    Registered User
    Join Date
    10-23-2020
    Location
    Chester
    MS-Off Ver
    365
    Posts
    3

    Re: Insert blank rows when data changes and calculate how many rows to insert

    Here's a sample file attached. To clarify I would like an automatic way to calculate and insert four blank rows after code 001 in column A so that it's 24 rows (a multiple of 6) before the next code, then one blank row after 002 (to bring to 18 rows) and also one 1 after 003 (to bring it to 24 rows).
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Insert blank rows when data changes and calculate how many rows to insert

    That can oly be done (AFAIK) with VBA (.xlsm file). Is that OK?
    Glenn



  5. #5
    Registered User
    Join Date
    10-23-2020
    Location
    Chester
    MS-Off Ver
    365
    Posts
    3

    Re: Insert blank rows when data changes and calculate how many rows to insert

    Thanks Glenn. A compromise solution would be to inserts 24 blank rows between every time the data (store code in column A) changes. That way I'll have e several blank sheets between each kind. Is there a way to tell excel to do that?

+ 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. Replies: 2
    Last Post: 10-28-2017, 10:51 AM
  2. Insert 3 blank rows after every 6 rows of existing data
    By jhiltabidel in forum Excel General
    Replies: 1
    Last Post: 02-01-2017, 12:20 AM
  3. Replies: 11
    Last Post: 07-30-2015, 12:35 PM
  4. [SOLVED] insert blank rows every nth row and copy down data to fill blank rows
    By surpass in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-06-2014, 02:55 PM
  5. Replies: 4
    Last Post: 09-16-2014, 10:48 AM
  6. How to create a macro to insert blank rows and copy data into blank rows?
    By zodiack101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2013, 01:18 PM
  7. [SOLVED] Macro to insert blank rows so that the total number of rows with data is equal to 1021
    By nsm1411 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-07-2013, 10:25 AM

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