Forum Discussion
How do I inserts a calculated number of blank rows when data changes?
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
- 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
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
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,
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)
