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

1. ## 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. ## 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. ## 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).

4. ## 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?

5. ## 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?

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