22 Macro Examples in Excel VBA

When it comes to boosting output or gaining time at work, macros are your best friend. Using the VBA coding language, we can automate anything from minor to large processes. We are aware that you may have occasionally considered some of Excel’s limits, but thanks to the VBA code, you can do away with them altogether. In this article, we will provide some handy example of VBA Macro code in Excel if you have trouble with VBA and are still a novice.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Easy Guidelines to Create 22 Most Useful VBA Macro Examples

VBA is a programming language that may be used for various tasks, and different types of users can use it for those tasks. You can launch the VBA editor using the Alt + F11 keyboard shortcut. In the section, we will generate a lot of  VBA code that is relatively easy for you to observe and how to use various valuable VBA Macro examples in different Excel applications.

  • Firstly, we will open the Developer tab.
  • Then, we will select the Visual Basic command.

Creating 22 Most Useful VBA Macro Examples in Excel

  • Here, the Visual Basic window will open.
  • After that, from the Insert option, we will choose the new Module to write a VBA code.


Example 1: Adding Serial Number

If you work with a lot of data, this macro code will enable you to automatically add serial numbers to your Excel spreadsheet.

Sub Add_Serial_Numbers()
'Declaring variable
Dim x As Integer
'Using For loop
For x = 1 To 10
Cells(x, 1).Value = x
Next x
End Sub
  • Therefore, to run the program, click the “Run” button or press F5. 

Adding Serial Number for creating VBA Example in Excel

  • After running this macro, it will add serial numbers in the spreadsheet from 1 to 10.


Example 2: Auto-fitting Columns

This code dynamically and rapidly aligns each column in your worksheet. Therefore, when you run this code, it will quickly auto-fit all the columns and identify all the cells in your worksheet.

Sub Auto_Fit_Columns()
Cells.Select
'Auto fit the entire column
Cells.EntireColumn.AutoFit
End Sub
  • Here, you will see there is some misalignment in the columns.

Auto-fitting Columns for Creating VBA Macro Example in Excel

  • Therefore, to run the program, click the “Run” button or press F5. 

  • Here, it will auto-fit every column in the below image.


Example 3: Auto-fitting Rows

This code will automatically fit each row in a worksheet. When you run this code, it will instantly auto-fit the entire row while selecting every cell in your worksheet.

Sub Auto_Fit_Rows()
Cells.Select
'Auto fit the row
Cells.EntireRow.AutoFit
End Sub
  • Similarly, you will observe there is some misalignment in the rows.

Auto-fitting Rows for Creating VBA Macro Example in Excel

  • Then, to run the program, click the “Run” button or press F5. 

  • Here, every row in the image below will automatically resize.

Read More: VBA Macro to Delete Row if Cell Contains Value in Excel (2 Methods)


Example 4: Printing Sheet Name

The active sheet will receive all the sheet names by this macro code. When you run this code, it will copy all of the accessible workbook sheets to the current sheet.

Sub Print_Sheet_Names()
'Declaring variable
Dim j As Integer
'Using For loop to count the number of sheets
For j = 1 To Sheets.Count
'Print all the sheets available in the workbook
Cells(j, 1).Value = Sheets(j).Name
Next j
End Sub
  • Therefore, to run the program, click the “Run” button or press F5. 

 Printing Sheet Name for Creating VBA Macro Example in Excel

  • After running this code, you will see this final output sheet, including all the available sheet names.

Read More: How to Alternate Row Colors in Excel Without Table (5 Methods)


Example 5: Inserting Multiple Worksheets

If you wish to add several worksheets to your workbook at once, you can use this code. You will see an input box to enter the total number of sheets you want to enter when you run this macro code. It is one of the time-saving VBA Macro example.

Sub Add_Multiple_Sheets()
'Declaring variable
Dim SheetsNumber As Integer
'Put the number of sheets
SheetsNumber = InputBox("Enter number of sheets to insert", "Enter number of sheets")
'Adding the additional sheets after the current active sheet
Sheets.Add After:=ActiveSheet, Count:=SheetsNumber
End Sub
  • Firstly, you will see the available sheets herein below image.

Inserting Multiple Worksheets for cReating VBA Macro Example in Excel

  • Then, to run the program, click the “Run” button or press F5. 

  • Here, an input box will open.
  • Then, select the number you want to wish to insert worksheets in your workbook. Here, we will select 2.
  • And, click OK.

  • Finally, you will notice two extra sheets, which are sheet4 and sheet3.

Read More: How to Assign Macro to Button in Excel (2 Easy Methods)


Example 6: Unhide Multiple Sheets

If a workbook you are using includes numerous sheets that are hidden, you must unhide each sheet one at a time. If there are numerous concealed sheets, this process could take some time. This code will display every worksheet in the workbook.

Sub Unhide_Multiple_Sheets()
'Declaring variable
Dim MySheet As Worksheet
'Using For Each loop
For Each MySheet In ActiveWorkbook.Worksheets
'All the sheets will be visible
MySheet.Visible = xlSheetVisible
Next MySheet
End Sub
  • There is only one sheet available in this workbook here.

Unhide Multiple Sheets for Creating VBA Macro Example in Excel

  • Then, to run the program, click the “Run” button or press F5. 

  • So, you will see all the worksheets herein in the below image after running this above macro code.


Example 7: Unhiding All Rows and Columns

This macro code will help you to unhide all the hidden rows and columns in your worksheet. If you receive a file from someone else and want to make sure there are no hidden rows or columns, this might be useful.

Sub Unhide_Rows_Columns()
'Unhide columns
Columns.EntireColumn.Hidden = False
'Unhide rows
Rows.EntireRow.Hidden = False
End Sub
  • You will not see here the C column and row 5.

Unhiding All Rows and Columns for Creating VBA Macro Example in Excel

  • After that, to run the program, click the “Run” button or press F5. 

  • Therefore, the below image will show you the hidden rows and columns after running this macro.

Read More: How to Make VBA Code Run Faster (15 Suitable Ways)


Example 8: Converting All Formulas into Values

If you want to know the conversion from the formulas to values, then you will use the following macro which makes your work easy.

Sub Convert_Formulas_To_Values()
'Specifying the formula range cells
With ActiveSheet.UsedRange
'Converting the formulas to values
.Value = .Value
End With
End Sub
  • Here, you will find all the formulas in the E column.

Converting All Formulas into Values for Creating VBA Macro Example in Excel

  • After that, to run the program, click the “Run” button or press F5. 

  • Finally, you will observe all the respective values according to their respective formulas.

Read More: How to Open Workbook from Path Using Excel VBA (4 Examples)


Example 9: Protecting All Worksheets

This is the code you can use to protect all of your worksheets at once. This code will go over each worksheet individually and secure it.

Sub Protect_Multiple_Sheets()
'Declaring variable
Dim MySheet As Worksheet
'Using For loop
For Each MySheet In Worksheets
'Protecting multiple sheets
MySheet.Protect
Next MySheet
End Sub
  • When your sheet remains unprotected, you can do everything you want.
  • You will see here the formula when we click on the cell because this sheet is not protected.

 Protecting All Worksheets for Creating VBA Macro Example in Excel

  • Then, to run the program, click the “Run” button or press F5. 

  • Finally, you will see the following image when you click any cell in your sheet after running this above macro code.


Example 10: Inserting a Row After Every Other Row

The following macro will help you to insert a row after every other row when you run this macro.

Sub Insert_Row_After_Every_Other_Row()
'Declaring Variables
Dim Xrng As Range
Dim MyRow As Integer
Dim j As Integer
Set Xrng = Selection
'Counting rows
MyRow = Xrng.EntireRow.Count
'using For loop
For j = 1 To CountRow
'Inserting row after every other row
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).Select
Next j
End Sub
  • Firstly, you will select the row where you want to insert the row.
  • Here, we will select row number 6.

 Inserting a Row After Every Other Row for Creating VBA Macro Example in Excel

  • Then, to run the program, click the “Run” button or press F5. 

  • As a result, you will notice the new blank row in the below image.

Read More: 6 Best Excel VBA Programming Books (For Beginners & Advanced Users)


Example 11: Highlighting Cells with Misspelled Words

Spell checking is not available in Excel like it is in Word or PowerPoint.Therefore, you can use this code to instantaneously highlight every cell that contains a spelling error.

Sub Check_Spelling_Error()
'Declaring variable
Dim MyCheck As Range
'Using For Each loop
For Each MyCheck In ActiveSheet.UsedRange
'Using this statement to check spelling mistake
If Not Application.CheckSpelling(Word:=MyCheck.Text) Then
'Coloring the misspelled words
MyCheck.Interior.Color = vbRed
End If
Next MyCheck
End Sub
  • Here, you will some spelling mistakes in some of the words in column B such as Watche, Compoter, and Digitel camera.

Highlighting Cells with Misspelled Words for Creating VBA Macro Example in Excel

  • Then, to run the program, click the “Run” button or press F5. 

  • Consequently, every highlighted misspelled word you have will show up in the below image after running the above macro.

Read More: How Different Is VBA from Other Programming Languages


Example 12: Erasing Blank Worksheets

This macro will erase all the blank worksheets from the workbook we are using while running the following macro code.

Sub Erasing_Blank_Sheets()
'Declaring variable
Dim MySheet As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Using For Each loop
For Each MySheet In ActiveWorkbook.Worksheets
'using this statement to Count blank sheets
If WorksheetFunction.CountA(MySheet.UsedRange) = 0 Then
'Deleting blank sheets
MySheet.Delete
End If
Next MySheet
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
  • Here, you will observe that blank sheet2 is available.

Erasing Blank Worksheets for Creating VBA Macro Example in Excel

  • Therefore, to run the program, click the “Run” button or press F5. 

  • As a result, there is no blank sheet in the workbook after running the above macro code.


Example 13: Inserting Different Color Code Index

This macro will insert numbers from 1 to 10 and their respective color index in the next column while running the following macro code.

Sub Insert_Multple_Colours()
'Using variable
Dim j As Integer
'Using For loop
For j = 1 To 10
Cells(j, 1).Value = j
'Inserting the color  code index
Cells(j, 2).Interior.ColorIndex = j
Next
End Sub
  • Therefore, to run the program, click the “Run” button or press F5. 

Inserting Different Color Code Index for Creating VBA Macro Example in Excel

  • Finally, you will observe the following output in the image with different color code indexes.


Example 14: Changing All to Upper Case Characters

This macro code works for converting all the text values to uppercase when you run this code.

Sub Convert_To_UPPER_Case()
'Declaring variable
Dim Xrng As Range
'Using For Each loop
For Each Xrng In Selection.Cells
'Using If Statement
If Xrng.HasFormula = False Then
'Specify the Range
Xrng.Value = UCase(Xrng.Value)
End If
Next Xrng
End Sub
  • Firstly, select column B to convert all the text values into upper case.

Changing All to Upper Case Characters for Creating VBA Macro Example in Excel

  • Therefore, to run the program, click the “Run” button or press F5. 

  • Lastly, the final results show all the uppercase text in the below image.


Example 15: Changing All to Lower Case Characters

This macro code works for converting all the text values to lowercase when you run this code.

Sub Convert_To_LOWER_Case()
'Declaring variable
Dim Xrng As Range
'Using For Each loop
For Each Xrng In Selection.Cells
'Using If Statement
If Xrng.HasFormula = False Then
'Specify the Range
Xrng.Value = LCase(Xrng.Value)
End If
Next Xrng
End Sub
  • Firstly, select column B to convert all the text values into lowercase.

Changing All to Lower Case Characters for Creating VBA Macro Example in Excel

  • Therefore, to run the program, click the “Run” button or press F5. 

  • The final results are displayed in the graphic below, which only has lowercase text.

Read More: List of 10 Mostly Used Excel VBA Objects (Attributes & Examples)


Example 16: Converting into Proper Case

This code will change a selection of text to the appropriate case, with the initial letter in uppercase and the other letters in lowercase.

Sub Convert_ProperCase()
'Declaring variable
Dim rng As Range
'Using For Each loop
For Each rng In Selection
'Reading the worksheet text range cells
If WorksheetFunction.IsText(rng) Then
'Converting all the selected range to proper case
rng.Value = WorksheetFunction.Proper(rng.Value)
End If
Next
End Sub
  • Here, select the B column first to convert the proper case.

Converting into Proper Case for Creating VBA Macro Example in Excel

  • Then, to run the program, click the “Run” button or press F5. 

  • Finally, the given image shows the proper case in letters with capital in the first letter and the rest in small letters.


Example 17: Sorting Worksheets Alphabetically

This macro code might be quite helpful if you have a workbook with lots of worksheets and you want to organize them alphabetically.

Sub Sort_Worksheets_Alphabetically()
'Fast the code running time
Application.ScreenUpdating = False
'Declaring variables
Dim MySheetCount As Integer, x As Integer, y As Integer
MySheetCount = Sheets.Count
'Using nested For loop to sort the worksheets alphabetically
For x = 1 To MySheetCount - 1
For y = x + 1 To MySheetCount
If UCase(Sheets(y).Name) < UCase(Sheets(x).Name) Then
Sheets(y).Move before:=Sheets(x)
End If
Next y
Next x
Application.ScreenUpdating = True
End Sub
  • In the below image here, all the sheet names are not in order alphabetically.

Sorting Worksheets Alphabetically for Creating VBA Macro Example in Excel

  • Then, to run the program, click the “Run” button or press F5. 

  • After running the above macro, you will all the sheet names in order alphabetically.

Read More: How to Use Select Case Statement in Excel VBA (2 Examples)


Example 18: Highlighting Multiple Blank Cells

While you can highlight blank cells utilizing conditional formatting or the go-to Special dialog box, it’s easier to use a macro if you need to do so frequently. Once built, you can save this macro in your own macro workbook or add it to the Quick Access Toolbar. VBA Macro example could be the way to get it in a faster approach.

Sub Highlight_Blank_Cells()
'Declaring variable
Dim MyData As Range
Set MyData = Selection
'Highligting the selected data range with color
MyData.Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = vbGreen
End Sub
  • Firstly, select all the data ranges from your worksheet.

Highlighting Multiple Blank Cells for Creating VBA Macro Example in Excel

  • Then, to run the program, click the “Run” button or press F5. 

  • So, while running after the macro, it will show you the below image with highlighted blank cells in your worksheet.


Example 19: Applying Paste Special

We do have the Paste Special VBA Macro example to copy data and paste it on another cell range, just like a worksheet. It enables us to paste the data exactly as it is, just the formulas, just the values, or all three in the same way. The range property function in VBA allows us to use Paste Special.

Sub Paste_Special()
'Copy the specified range of data set
Range("B2:E10").Copy
'Paste in the specified range
Range("G2:J10").PasteSpecial xlPasteAll
End Sub
  • Here is our initial data set which we want to paste into another data range.

Applying Paste Special for Creating VBA Macro Example in Excel

  • Therefore, to run the program, click the “Run” button or press F5. 

  • Finally, you will see the same data set on the right side of the worksheet.

Read More: How to Import Data from a Website to Excel?


Example 20: Refreshing Pivot Tables in Workbook

To apply the VBA Macro example in a Table, the following will help you to update your pivot table easily. If your workbook contains multiple pivot tables, you can use this code to update them all at once.

Sub Refresh_PivotTables()
'Declaring variable
Dim MyTable As PivotTable
'Using For loop
For Each MyTable In ActiveSheet.PivotTables
'Refreshing the table
MyTable.RefreshTable
Next MyTable
End Sub
  • Here, we have created our pivot table according to our respective data sets.

Refreshing Pivot Tables in Workbook for Creating VBA Macro Example in Excel

  • Then, if we change the C6 cell value from 670 to 650, you will see that the total price in the E6 cell is updated below according to the respective changing value.
  • But, at position 3 in the pivot table, there is no change occur.

  • Therefore, to run the program, click the “Run” button or press F5.

  • After running the above macro code, it will show the updated value at position 3  in the pivot table according to our corresponding dataset.

Read More: How to Use VBA Modules in Excel (8 Simple Ways)


Example 21: Applying VLOOKUP with VBA

The lookup function aids in finding data from the primary table using a single lookup value. That sort of data structure is not necessary for the VBA LOOKUP function. Whether the result column is to the right or left of the lookup value does not matter for the LOOKUP function. Even so, it can easily retrieve the data. The VBA Macro example, it’s an easier way to perform VLOOKUP.

Sub VLookup()
'Specifyinh the Lookup value, Lookup vector, and Result vector
Range("H5").Value = WorksheetFunction.Lookup(Range("G5") _
.Value, Range("B5:B10"), Range("E5:E10"))
End Sub
  • So, we want to find the value of the total price for the Mobile Phone and put it in the H5 cell.

Applying VLOOKUP with VBA in Excel

  • Therefore, to run the program, click the “Run” button or press F5

  • As a result, you will find the value of the total price for mobile phones in the H5 cell here.


Example 22: Creating User Defined Function

Microsoft provides us with a variety of built-in capabilities to make the VBA Macro example perform more quickly. However, we can program our own functions using VBA, often referred to as “User Defined Functions” (UDF). In Excel VBA, they are also referred to as “custom functions.”

Function Total_Price(x As Long, y As Long) As Long
'Using Product formula
Total_Price = x * y
End Function
  • Firstly, select the E5 cell and write the following formula which we have created using VBA macro.
=Total_Price(C5,D5)
  • Secondly, click ENTER and use the Fill Handle tool to drag it down from the E5 cell to the E10 cell.

Creating User Defined Function for Creating VBA Macro Example in Excel

  • Consequently, you will find all results in the E column here in the below image.

Read More: How to Write VBA Code in Excel (With Easy Steps)


Conclusion

In this article, we’ve covered a lot of useful VBA Macro example for creating VBA code in Excel. We sincerely hope you enjoyed and learned a lot from this article. Additionally, if you want to read more articles on Excel, you may visit our website, ExcelDemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Related Articles

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo