Sowmik Chowdhury
Labels are useful to us in a lot of ways. You can create labels in excel with the help of Microsoft Word. But, in some cases, this may be a lot of hassle and time-consuming. So, you might want to create labels in Excel without Word. This article demonstrates a step-by-step guide of how to create labels in Excel without Word.
Download Practice Workbook
You can download the practice workbook from the link below.
What Are Labels?
Generally speaking, a label is a sticker or a piece of paper describing the object or product it is attached to. Labels are used in wide arrays of applications. For example, people need to create labels for addresses so that they can attach them to the envelope. Also, we can see labels attached to the hardware in our computers so that we can identify them and know about their specifications.
4 Steps to Create Labels Without Word in Excel Using VBA
It is very much convenient to create labels in Excel without the help of Microsoft Word. Moreover, it saves a lot of time and hassle.
Now, let’s assume you have a dataset with a list of Addresses you want to send your Christmas cards. At this point, you want to create labels for these Addresses in Excel without using Word. In this case, you can follow the sets of steps below to do so.
Step 01: Copy Data to a New Sheet in Excel
- First, copy the addresses in the column and paste them into a New Sheet starting from cell A1.
Step 02: Open VBA Window and Create a Module in Excel
- Then, press ALT + F11 to open the VBA window.
- Now, select the sheet you are currently working on and Right-Click on it. In this case, it is Sheet 2.
- Next, sequentially select Insert > Module.
Eventually, these sets of steps will open the VBA window and insert a new Module in your sheet.
Step 03: Insert the Code in Excel
- At this point, copy the following code and paste it in the blank box.
'This Code Will Create Labels in Excel
Sub Createlabels()
Application.Run "AskForColumn"
Cells.Select
Selection.RowHeight = 75.75
Selection.ColumnWidth = 34.14
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
Sub AskForColumn()
Dim refrg As Range
Dim vrb As Long
Dim dat As Long
Set refrg = Cells(Rows.Count, 1).End(xlUp)
dat = 1
On Error Resume Next
incolno = InputBox("Enter Number of Columns Desired")
For vrb = 1 To refrg.Row Step incolno
Cells(dat, "A").Resize(1, incolno).Value = _
Application.Transpose(Cells(vrb, "A").Resize(incolno, 1))
dat = dat + 1
Next
Range(Cells(dat, "A"), Cells(refrg.Row, "A")).ClearContents
End Sub
💡 Code Explanation:
At this stage, I will explain the code used above. As you can see, there are two parts of this code. One part contains the sub AskForColumn () which will be termed as Part 01,and the other contains the sub Createlabels() which will be termed as Part 02. Now, I will explain both these parts one by one.
Part 01:
At this point, I have divided the code into different sections and numbered each of the sections in the following screenshot. Consequently, here I will explain each section according to their number.
- Section 1: This section creates a sub named AskForColumn.
- Section 2: Here, we declare different variables.
- Section 3: In this section, we count the number of rows in the column.
- Section 4: Now, here we create an InputBox to ask for the number of columns.
- Section 5: In this section, we run a For loop. Also, we use the Step keyword to run the loop the number of times we put in the InputBox.
- Section 6: At this stage, here we use this section to distribute the cell of the column into different rows and columns by using application Transpose and property Resize.
- Section 7: Finally, we delete the extra contents in this section.
Part 02:
In this case, also, I have divided the code into different sections and numbered each of the sections in the following screenshot. Consequently, here I will explain each section according to their number.
- Section 1: This section creates a sub named Createlabels().
- Section 2: In this section, we call the sun AskForColumn() to run.
- Section 3: At this point, this section defines the Cell Format for all the cells using the Cells property.
Step 04: Run the Code to Create Labels Without Word in Excel
- Now, press F5 to run the sub Createlabels ().
- Consequently, you will see a box appear on your screen like the screenshot below.
- At this point, insert the number of columns you want your labels in.
- Then, click on OK.
- Finally, you will have your labels created like the screenshot below.
💡 Notes:
- This method will only work if you have your data in a single column.
- After creating the labels, you can add borders using the All Borders options in the Home tab.
- Also, make sure you run the sub Createlabels(). In this case, if you run the sub, AskForColumn () you will not get your desired result.
- Moreover, do not enter anything other than the addresses in column A.
Read More: How to Create Labels in Word from Excel List (Step-by-Step Guideline)
How to Print Labels in Excel Without Word
After creating the labels using the above-mentioned steps, now you may want to print the labels. Now, if you want to do so, you may follow the steps below.
Steps:
- First, go to the Page Layout tab from the top of the page.
- Next, click on the Page Setup button.
- After that, go to Margins.
- Now, edit the Top and Bottom margins to 0.5 and then edit the Left and Right margins to 0.215.
- Consequently, click on OK.
- Now, press CTRL + P to open the Print menu.
- At this point, click on No Scaling and 4 options will show up.
- Next, from those options select Fit All Columns on One Page.
- Finally, you are ready to print.
- In this case, your print preview will be like the screenshot below.
Read More: How to Mail Merge Labels from Excel to Word (With Easy Steps)
Conclusion
Last but not the least, I hope you found what you were looking for from this article. If you have any queries, please drop a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
