FWIW, an alternative approach would be to use a Directory merge. Setting this up for the first use is a bit fiddly but, from then on there's no need to modify the data source. It also changes the way the output is generated, from an across-then-down arrangement to a down-then-across arrangement.
What you do is:
1. In the
Labels dialog, click
New Document to create a new document based on the label definition you have chosen.
2. Display table gridlines (
Table Tools | Layout | Table | View Gridlines) and nonprinting characters (
Ctrl+* or
Home | Paragraph | Show/Hide ¶) so you can see what you're doing.
3. Delete all cells except the first. If there is a spacer column, note its width beforehand.
4. On the
Page Layout tab, in the Page Setup group, click
Columns and choose
More Columns...
5. In the
Columns dialog, select however many labels across your label stationery had
6. In the
Spacing box, set the width to the original spacer column width or, if it had none, 0
7. If necessary, set the column width to your label width.
8. Change the mailmerge type to 'Directory'.
From then on, when you run the mailmerge and send the output to a new document. Once the merge is done, simply insert as many empty cells as you need (e.g. via the insertion of column breaks and/or by however many cells you need for the blanks then choosing
Table Tools | Layout | Insert Above) before the first populated cell.
By adding the following macro to your Directory mailmerge main document, clicking on the 'Edit Individual Documents' button will intercept the merge, allowing you to specify how many empty/used labels there are, before sending the output to a new document. It will work with any data source and does so by modifying the mailmerge output and inserting however many empty rows of labels are required. With this approach, you should never lose an unused label.
Code:
Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim i As Long, j As Long
j = CLng(InputBox("How many labels have been used on the first sheet?", "Skip used labels"))
ActiveDocument.MailMerge.Execute
With ActiveDocument
.Rows.AllowBreakAcrossPages = False
For i = 1 To j
.Rows.Add .Rows(1)
Next
End With
'.PrintOut
'.Close False
End With
Application.ScreenUpdating = True
End Sub
Note: The above code sends the merge output to a new document, not direct to printer, but it can be printed from there. To automate that and have the merge perform like a merge to print, un-comment the lines:
Code:
'.PrintOut
'.Close False
You should also note that this won’t be especially useful for using left-over labels from a conventional label merge. However it does obviate the need to modify the data source every time.