Top 100 Useful Excel Macro [VBA] Codes Examples

Macro codes can save you a ton of time.

You can automate small as well as heavy tasks with VBA codes.

And do you know?

With the help of macros...

...you can break all the limitations of Excel which you think Excel has.

And today, I have listed some of the useful codes examples to help you become more productive in your day to day work.

You can use these codes even if you haven't used VBA before that.

But here's the first thing to know:

What is a Macro Code?

In Excel, macro code is a programming code which is written in VBA (Visual Basic for Applications) language.

The idea behind using a macro code is to automate an action which you perform manually in Excel, otherwise.

For example, you can use a code to print only a particular range of cells just with a single click instead of selecting the range -> File Tab -> Print -> Print Select -> OK Button.

How to use a Macro Code in Excel

  • Go to your developer tab and click on "Visual Basic" to open the Visual Basic Editor.
    click-on-visual-basic-editor-before-you-use-these-useful-macros-for-excel
  • On the left side in "Project Window", right click on the name of your workbook and insert a new module.
    add-module-to-paste-these-useful-macros-for-excel
  • Just paste your code into the module and close it.
    use-useful-macro-codes-examples-by-pasting-them-into-vb-editor
  • Now, go to your developer tab and click on the macro button.
    useful-macro-codes-examples-to-use-from-macro-options
  • It will show you a window with a list of the macros you have in your file from where you can run a macro from that list.
    useful-macro-codes-examples-list-from-macro-option

List of top 100 macro Examples (CODES) for VBA beginners

I’ve added all the codes into specific categories so you can find your favorite codes quickly. Just read the title and click on it to get the code.

note

Basic Codes

These VBA codes will help you to perform some basic tasks in a flash which you frequently do in your spreadsheets.

1. Add Serial Numbers

Sub AddSerialNumbers()
Dim i As Integer
On Error GoTo Last
i = InputBox("Enter Value", "Enter Serial Numbers")
For i = 1 To i
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Activate
Next i
Last:Exit Sub
End Sub

2. Insert Multiple Columns

This code helps you to enter multiple columns in a single click. When you run this code it asks you the number columns you want to add and when you click OK, it adds entered number of columns after the selected cell.

Sub InsertMultipleColumns()
Dim i As Integer
Dim j As Integer
ActiveCell.EntireColumn.Select
On Error GoTo Last
i = InputBox("Enter number of columns to insert", "Insert Columns")
For j = 1 To i
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightorAbove
Next j
Last: Exit Sub
End Sub

If you want to add columns before the selected cell, replace the xlToRight to xlToLeft in the code.

3. Insert Multiple Rows

Sub InsertMultipleRows()
Dim i As Integer
Dim j As Integer
ActiveCell.EntireRow.Select
On Error GoTo Last
i = InputBox("Enter number of columns to insert", "Insert Columns")
For j = 1 To i
Selection.Insert Shift:=xlToDown, CopyOrigin:=xlFormatFromRightorAbove
Next j
Last: Exit Sub
End Sub

4. Auto Fit Columns

Sub AutoFitColumns()
Cells.Select
Cells.EntireColumn.AutoFit
End Sub

5. Auto Fit Rows

You can use this code to auto-fit all the rows in a worksheet. When you run this code it will select all the cells in your worksheet and instantly auto-fit all the row.

Sub AutoFitRows()
Cells.Select
Cells.EntireRow.AutoFit
End Sub

6. Remove Text Wrap

This code will help you to remove text wrap from the entire worksheet with a single click. It will first select all the columns and then remove text wrap and auto fit all the rows and columns.

Sub RemoveTextWrap()
Range("A1").WrapText = False
End Sub

7. Unmerge Cells

This code simply uses the unmerge options which you have on the HOME‌ tab. The benefit of using this code is you can add it to the QAT and unmerge all the cell in the selection.

Sub UnmergeCells()
Selection.UnMerge
End Sub

And if you want to un-merge a specific range you can define that range in the code by replacing the word selection.

8. Open Calculator

In Windows, there is a specific calculator and by using this macro code you can open that calculator directly from Excel.

Sub OpenCalculator()
Application.ActivateMicrosoftApp Index:=0
End Sub

As I mentioned that it’s for windows and if you run this code in the MAC version of VBA you’ll get an error.

9. Add Header/Footer Date

This macro adds a date to the header when you run it. It simply uses the tag "&D" for adding the date. You can also change it to the footer or change the side by replacing the "" with the date tag.

Sub DateInHeader()
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&D"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
End Sub

And if you want to add a specific date instead of the current date you can replace the "&D" tag with that date from the code.

10. Custom Header/Footer

When you run this code, it shows an input box that asks you to enter the text which you want to add as a header, and once you enter it click OK.

Sub CustomHeader()
Dim myText As String
myText = InputBox("Enter your text here", "Enter Text")
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = myText
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
End Sub

If you see this closely you have six different lines of code to choose the place for the header or footer. Let’s say if you want to add left-footer instead of center header simply replace the “myText” to that line of the code by replacing the "" from there.

Formatting Codes

These VBA codes will help you to format cells and ranges using some specific criteria and conditions.

11. Highlight Duplicates from Selection

This macro will check each cell of your selection and highlight the duplicate values

You can also change the color from the code.

Sub HighlightDuplicateValues()
Dim myRange As Range
Dim myCell As Range
Set myRange = Selection
For Each myCell In myRange
If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 36
End If
Next myCell
End Sub

12. Highlight the Active Row and Column

I really love to use this macro code whenever I have to analyze a data table.

Here are the quick steps to apply this code.

  1. Open VBE (ALT + F11).
  2. Go to Project Explorer (Ctrl + R, If hidden).
  3. Select your workbook & double click on the name of a particular worksheet in which you want to activate the macro.
  4. Paste the code into it and select the “BeforeDoubleClick” from event drop down menu.
  5. Close VBE and you are done.

Remember that, by applying this macro you wi ll not able to edit the cell by double click.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range,
Cancel As Boolean)
Dim strRange As String
strRange = Target.Cells.Address & "," Target.Cells.EntireColumn.Address & "," & _
Target.Cells.EntireRow.Address
Range(strRange).Select
End Sub

13. Highlight Top 10 Values

Just select a range and run this macro and it will highlight top 10 values with the green color.

Sub TopTen()
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).S
tFirstPriority
With Selection.FormatConditions(1)
.TopBottom = xlTop10Top
.Rank = 10
.Percent = False
End With
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

14. Highlight Named Ranges

If you are not sure about how many named ranges you have in your worksheet then you can use this code to highlight all of them.

Sub HighlightRanges()
Dim RangeName As Name
Dim HighlightRange As Range
On Error Resume Next
For Each RangeName In ActiveWorkbook.Names
Set HighlightRange = RangeName.RefersToRange
HighlightRange.Interior.ColorIndex = 36
Next RangeName
End Sub

15. Highlight Greater than Values

Once you run this code it will ask you for the value from which you want to highlight all greater values.

Sub HighlightGreaterThanValues()
Dim i As Integer
i = InputBox("Enter Greater Than Value", "Enter Value")
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, Formula1:=i
Selection.FormatConditions(Selection.FormatConditions.Count).S
tFirstPriority
With Selection.FormatConditions(1)
.Font.Color = RGB(0, 0, 0)
.Interior.Color = RGB(31, 218, 154)
End With
End Sub

16. Highlight Lower Than Values

Once you run this code it will ask you for the value from which you want to highlight all lower values.

Sub HighlightLowerThanValues()
Dim i As Integer
i = InputBox("Enter Lower Than Value", "Enter Value")
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlLower, Formula1:=i
Selection.FormatConditions(Selection.FormatConditions.Count).S
tFirstPriority
With Selection.FormatConditions(1)
.Font.Color = RGB(0, 0, 0)
.Interior.Color = RGB(217, 83, 79)
End With
End Sub

17. Highlight Negative Numbers

Select a range of cells and run this code. It will check each cell from the range and highlight all cells the where you have a negative number.

Sub highlightNegativeNumbers()
Dim Rng As Range
For Each Rng In Selection
If WorksheetFunction.IsNumber(Rng) Then
If Rng.Value < 0 Then
Rng.Font.Color= -16776961
End If
End If
Next
End Sub

18. Highlight Specific Text

Suppose you have a large data set and you want to check for a particular value. For this, you can use this code. When you run it, you will get an input box to enter the value to search for.

Sub highlightValue()
Dim myStr As String
Dim myRg As Range
Dim myTxt As String
Dim myCell As Range
Dim myChar As String
Dim I As Long
Dim J As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count> 1 Then
myTxt= ActiveWindow.RangeSelection.AddressLocal
Else
myTxt= ActiveSheet.UsedRange.AddressLocal
End If
LInput: Set myRg= Application.InputBox("please select the data
range:", "Selection Required", myTxt, , , , , 8)
If myRg Is Nothing Then
Exit Sub
If myRg.Areas.Count > 1 Then
MsgBox"not support multiple columns" GoToLInput
End If
If myRg.Columns.Count <> 2 Then
MsgBox"the selected range can only contain two columns "
GoTo LInput
End If
For I = 0 To myRg.Rows.Count-1
myStr= myRg.Range("B1").Offset(I, 0).Value
With myRg.Range("A1").Offset(I, 0)
.Font.ColorIndex= 1
For J = 1 To Len(.Text)
Mid(.Text, J, Len(myStr)) = myStrThen
.Characters(J, Len(myStr)).Font.ColorIndex= 3
Next
End With
Next I
End Sub

19. Highlight Cells with Comments

To highlight all the cells with comments use this macro.

Sub highlightCommentCells()
Selection.SpecialCells(xlCellTypeComments).Select
Selection.Style= "Note"
End Sub

20. Highlight Alternate Rows in the Selection

By highlighting alternate rows you can make your data easily readable. And for this, you can use below VBA code. It will simply highlight every alternate row in selected range.

Sub highlightAlternateRows()
Dim rng As Range
For Each rng In Selection.Rows
If rng.RowMod 2 = 1 Then
rng.Style= "20% -Accent1"
rng.Value= rng^ (1 / 3)
Else
End If
Next rng
End Sub

21. Highlight Cells with Misspelled Words

If you find hard to check all the cells for spelling error then this code is for you. It will check each cell from the selection and highlight the cell where is a misspelled word.

Sub HighlightMisspelledCells()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
If Not Application.CheckSpelling(word:=rng.Text) Then
rng.Style= "Bad" End If
Next rng
End Sub

22. Highlight Cells With Error in the Entire Worksheet

To highlight and count all the cells in which you have an error, this code will help you. Just run this code and it will return a message with the number error cells and highlight all the cells.

Sub highlightErrors()
Dim rng As Range
Dim i As Integer
For Each rng In ActiveSheet.UsedRange
If WorksheetFunction.IsError(rng) Then
i = i + 1 rng.Style = "bad"
End If
Next rng
MsgBox "There are total " & i & " error(s) in this worksheet."
End Sub

23. Highlight Cells with a Specific Text in Worksheet

This code will help you to count the cells which have a specific value which you will mention and after that highlight all those cells.

Sub highlightSpecificValues()
Dim rng As Range
Dim i As Integer
Dim c As Variant
c = InputBox("Enter Value To Highlight")
For Each rng In ActiveSheet.UsedRange
If rng = c Then
rng.Style = "Note"
i = i + 1
End If
Next rng
MsgBox "There are total " & i &" "& c & " in this worksheet."
End Sub

24. Highlight all the Blank Cells Invisible Space

Sometimes there are some cells which are blank but they have a single space and due to this, it’s really hard to identify them. This code will check all the cell in the worksheet and highlight all the cells which have a single space.

Sub blankWithSpace()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
If rng.Value = " " Then
rng.Style = "Note"
End If
Next rng
End Sub

25. Highlight Max Value In The Range

It will check all the selected cells and highlight the cell with the maximum value.

Sub highlightMaxValue()
Dim rng As Range
For Each rng In Selection
If rng = WorksheetFunction.Max(Selection) Then
rng.Style = "Good"
End If
Next rng
End Sub

26. Highlight Min Value In The Range

It will check all the selected cells and highlight the cell with the Minimum value.

Sub highlightMinValue()
Dim rng As Range
For Each rng In Selection
If rng = WorksheetFunction.Min(Selection) Then
rng.Style = "Good"
End If
Next rng
End Sub

27. Highlight Unique Values

This codes will highlight all the cells from the selection which has a unique value.

Sub highlightUniqueValues()
Dim rng As Range
Set rng = Selection
rng.FormatConditions.Delete
Dim uv As UniqueValues
Set uv = rng.FormatConditions.AddUniqueValues
uv.DupeUnique = xlUnique
uv.Interior.Color = vbGreen
End Sub

28. Highlight Difference in Columns

Using this code you can highlight the difference between two columns (corresponding cells).

Sub columnDifference()
Range("H7:H8,I7:I8").Select
Selection.ColumnDifferences(ActiveCell).Select
Selection.Style= "Bad"
End Sub

29. Highlight Difference in Rows

And by using this code you can highlight difference between two row (corresponding cells).

Sub rowDifference()
Range("H7:H8,I7:I8").Select
Selection.RowDifferences(ActiveCell).Select
Selection.Style= "Bad"
End Sub