How do I inserts a calculated number of blank rows when data changes?

Copper Contributor

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.

8 Replies

@Floatyman 

Here is an example as an approach, where you can select how many lines you would like to insert each time.

Passwort: Floatyman

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@NikolinoDE Thanks.  How did you do this? Was it using Macros and if so how do I do the same?  Please explain

 

regards,

 

@Floatyman 

Here is the code...is in German vba commands.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim zeilenanzahl As Long
Dim x As Integer
Dim i As Integer
x = 1
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Target.Column = 1 And Target.Row > 2 Then
Worksheets("2021").Unprotect Password:="Floatyman"
On Error GoTo Notausgang
zeilenanzahl = InputBox("How many rows?", "Insert rows")
With ActiveCell
For i = 1 To zeilenanzahl
If ActiveCell = "" Then
.EntireRow.Copy
.EntireRow.Offset(x, 0).Insert shift:=xlDown
.EntireRow.Offset(x, 0).PasteSpecial
ActiveCell = 1
.EntireRow.Offset(x, 0).SpecialCells(xlCellTypeConstants).ClearContents
Else
.EntireRow.Copy
.EntireRow.Offset(x, 0).Insert shift:=xlDown
.EntireRow.Offset(x, 0).PasteSpecial
.EntireRow.Offset(x, 0).SpecialCells(xlCellTypeConstants).ClearContents
ActiveCell.Offset(-x, 0).Select
End If
x = x + 1
Next i
.Offset(1, 0).Select
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Sheets("2021").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, _
AllowInsertingColumns:=True, AllowSorting:=True, AllowFiltering:=True, _
Password:="Floatyman"
Notausgang:
Cancel = True
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Sheets("2021").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True, _
AllowInsertingColumns:=True, AllowSorting:=True, AllowFiltering:=True, _
Password:="Floatyman"
End If
End Sub

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

Nikolino

I know I don't know anything (Socrates)

Huge apologies but I'm lacking the knowledge here. I paste the code into Visual Basic in the developer ribbon, and run it but nothing seems to happen.

How do I run the code please?
ALT + F11

Select the sheet
paste the code on the right side
Safe sheet

...and run.

If you can't do it with my instructions (I'm not the best at explaining :), you can send with a file (without sensitive data, please) and we will try to finish it for you. A service of the house :)

Ps. Please just be patient, could maybe (which I hope not) take a little longer, but maybe only if there is work to be done.

Nikolino
I know I don't know anything (Socrates)






But if you mean how to run this code in the worksheet. Then click from cell A2 (only possible in column A) and a message window appears where you can insert the number of rows. If it is, then sorry the translation is not the best :) Thank you for your understanding and patience Nikolino I know I don't know anything (Socrates)

@NikolinoDE Thank you for your help.  I paste the code in and Excel asks me to name and create a Macro, which I do.  Then Run, but see no difference so I must be doing something wrong.

 

Attached is a file.  In column A, when the code changes I would like 10 blank rows (or ideally a number rounded to up a multiple of 6) to be inserted to separate these codes into groups on the sheet.

 

regards

@Floatyman 

 

Ready to use! :)

 

Simply left double-click on column A (from A2) with the mouse and a user form will appear. There you enter how many lines you want and they will appear.

 

Tested and it works for me without problems.

 

I wish you continued success with Excel (the coolest invention since chocola... uh ... Microsoft! :)))

And keep asking here - I just taught myself Excel with the help of this forum :).

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here