Useful Excel VBA Macro Codes Examples

VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft.

It is primarily used to automate repetitive tasks in Microsoft Office applications like Excel, Word, Outlook, and Access.

I have been a VBA user for 10+ years now, and I can tell you it’s a fantastic way to automate simple as well as complex tasks. I have saved hundreds (if not thousands) of hours with very simple codes of VBA.

In this article, I have compiled a list of 100 useful VBA codes that should get you started. For each code, I have focussed on showing you how to get one thing done. Once you get comfortable with VBA, you can, of course, combine and get multiple things done with a simple click (or keyboard shortcut) using VBA.

Where is the VBA Code in Excel?

In Excel, VBA code is stored in the VBA Editor, which is a separate interface within Excel designed for writing and managing VBA code.

Any code that you write (or copy from this article) needs to be placed in the VB Editor.

How to Access the VB Editor in Excel?

To access the VBA editor in Excel, you can follow these steps:

1. Press Alt + F11 (hold the ALT key and press the F11 key). This shortcut opens the VBA Editor, where you can write, view, and edit VBA code. You can also click on the Developer tab and then click on the VB Editor icon to open the VB Editor.
2. In the VBA editor, you can insert modules by clicking on the Insert option and then clicking on Module. A module is where you write/put your VBA code.

How to Run the VBA Code?

Once you have the VBA code in the VB Editor, here are the steps to run it:

1. Click on the Developer tab and then click on the Macros icon. This will open the Macro dialog box. Alternatively, you can also use the keyboard shortcut ALT + F8.
2. Select the macro name that you want to run in the Macro dialog box
3. Click on the Run button.

Ensure the workbook containing your VBA code is saved as a Macro-Enabled Workbook (*.xlsm) to preserve the VBA code. Also, be cautious while running macros, especially from unknown sources, as they can contain harmful code.

Best Way to Learn VBA (Free Course)

For beginners, it’s helpful to start with recording macros to see how the generated VBA code looks and then gradually progress to writing your own code.

There are various online resources, tutorials, and forums available that provide in-depth knowledge and assistance for learning VBA.

I recommend checking out the FREE Excel VBA course by my friend and mentor, Sumit Bansal.

Top 100 Useful VBA Macro Code Examples

I have categorized the list of macros, so it should be easier for you to go through them.

Basic Excel VBA Macros

Let’s first start with the absolute basics, and then we will move on to some advanced examples.

Insert Text/Value in a Cell

Sub InsertTextinCell()
    ' Target the cell "A1" in the active worksheet
    Range("A1").Value = "Excel is Awesome"
End Sub

The above code uses the Range(“A1”).Value to target cell “A1” and insert the text “Excel is Awesome” into it.

Another way of doing the same is by using Cells instead of Range objects.

Below is the code that uses the Cells object to enter the text “Excel is Awesome” in cell A1.

Sub InsertTextinCell ()
    ' Target the cell in row 1 and column 1 (A1) in the active worksheet
    Cells(1, 1).Value = "Excel is Awesome"
End Sub

Display Message Box

In VBA (Visual Basic for Applications), you can display a message box using the MsgBox function. This function is very useful for displaying information to the user.

Advanced Use Case: I often use this with complex VBA code where I need to identify errors.

Here’s a simple example of how to display a message box with a simple message:

Sub ShowMessageBox()
' MsgBox function to show a message box with specified text
    MsgBox "This is a Message Box"
End Sub

When this VBA macro runs, a message box will appear displaying the message “This is a Message Box” and an OK button. Once you click on OK, the message box will close.

Apply Color to a Cell

Below is the VBA code that applies thered color to cell A1 in the active worksheet

Sub ApplyColortoCell()
    Range("A1").Interior.Color = RGB(255, 255, 0)
End Sub

This VBA code will change the background color of cell A1 in “Sheet1” to red.

It uses the Interior.Color property of the Range to set the background color, and the RGB(255, 0, 0) function specifies the red color.

Create a New Worksheet

Below is the VBA code that will create a new worksheet at the end of all existing worksheets and name it “NewSheet”.

Sub AddNewWorksheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "NewSheet"
End Sub

In the above code, we start by declaring a variable named ws to hold the new worksheet we’re about to create.

The Set ws = ThisWorkbook.Sheets.Add(…) line actually creates the new worksheet and assigns it to our variable.

We place this newly created sheet it at the end of the existing sheets by using After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count). Finally, we rename the new worksheet to “NewSheet” using the Name property of the ws object.

Copy and Paste Data

The below VBA code will copy the cells A1:A10 in Sheet1 and then paste it in B1:B10 in the same sheet.

Sub CopyAndPasteInSameWorksheet()

    ' Declare a variable to represent the worksheet
    Dim ws As Worksheet
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Copy data from Range A1:A10
    ws.Range("A1:A10").Copy
    
    ' Paste data to Range B1:B10
    ws.Range("B1:B10").PasteSpecial Paste:=xlPasteAll
    
    ' Clear Clipboard to remove the "marching ants" around copied area
    Application.CutCopyMode = False
End Sub

The code copies data from the range A1:A10 using the .Copy method and pastes it into the range B1:B10 using the .PasteSpecial method with xlPasteAll, which ensures that everything from the source range—including values, formatting, and formulas—is copied over to the destination range.

After the operation, the clipboard is cleared to remove the “marching ants” outline from the copied cells.

If you only want to copy the values, you can use Paste:=xlPasteValues instead of Paste:=xlPasteAll

In the above code, we have copied the cells in the same sheet. However, you can also use VBA to copy the cells into any other sheet in the same workbook or even another workbook.

Below is the VBA code that would copy the data from Sheet1 and then copy it into Sheet2 in the same workbook.

Sub CopyAndPasteData()
       
    ' Copy data from Range A1:A10 in Sheet1
    Worksheets("Sheet1").Range("A1:A10").Copy
    
    ' Paste data to Range B1:B10 in Sheet2
    Worksheets("Sheet2").Range("B1:B10").PasteSpecial Paste:=xlPasteValues
    
    ' Clear Clipboard to remove the "marching ants" around copied area
    Application.CutCopyMode = False
End Sub

Hide a Row / Unhide a Row

Below the vb code that would hide row number 5 in Sheet1

Sub HideRow()
    ' Declare a variable to represent the worksheet
    Dim ws As Worksheet
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Hide Row 5
    ws.Rows(5).Hidden = True
End Sub

This VBA code snippet focuses on hiding row 5 within a worksheet named “Sheet1”. To accomplish this, we use a variable named ws to represent the worksheet we’re working on.

The Rows(5).Hidden = True line is where the actual hiding of the row occurs. The .Hidden property is set to True to make the row invisible.

If you want to unhide a row, you can change the .Hidden property to False.

For example, the below code would unhide row 5.

Sub UnhideRow()
    ' Declare a variable to represent the worksheet
    Dim ws As Worksheet
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Unhide Row 5
    ws.Rows(5).Hidden = False
End Sub

Activate Another Worksheet

Sub ActivateWorksheet()
    ' Declare a variable to represent the workbook
    Dim wb As Workbook
    
    ' Set the workbook
    Set wb = ThisWorkbook
    
    ' Activate the worksheet named "Sheet2"
    wb.Worksheets("Sheet2").Activate
End Sub

In this VBA example, the code activates a worksheet named “Sheet2” within the current workbook.

We first define a variable wb to represent the workbook we’re in, and then we set it to ThisWorkbook, which refers to the workbook containing the macro code.

The line wb.Worksheets(“Sheet2”).Activate takes care of switching to “Sheet2” by using the .Activate method.

Open a New Workbook

Sub OpenNewWorkbook()
    ' Declare a variable to represent the new workbook
    Dim newWb As Workbook
    
    ' Create a new workbook and assign it to the variable
    Set newWb = Workbooks.Add
    
    ' Optionally, you can set the name of the new workbook
    newWb.SaveAs "C:\Path\To\Your\NewWorkbook.xlsx"
End Sub

In this VBA code, the Workbooks.Add method is used to create a new workbook.

We declare a variable named newWb to hold the new Workbook object and then use the Set keyword to assign the newly created workbook to this variable.

Optionally, you can save the new workbook with a specific name using the SaveAs method, where you can provide the full path and name of the file.

Delete Content of a Cell

Sub DeleteCellContent()
    ' Declare a variable to represent the worksheet
    Dim ws As Worksheet
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Clear the content of cell A1
    ws.Range("A1").ClearContents
End Sub

In this VBA example, the code focuses on deleting the content of cell A1 within the worksheet named “Sheet1”.

We use a variable ws to represent the worksheet we’re working with.

The Range(“A1”).ClearContents line takes care of deleting the content of the cell. It uses the ClearContents method to remove any values, leaving formatting intact.

If you want to delete everything, including the formatting, you can use the below code:

Sub DeleteCellContent()
    ' Declare a variable to represent the worksheet
    Dim ws As Worksheet
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Clear the content of cell A1
    ws.Range("A1").Clear
End Sub

VBA Macros Codes Involving Cells/Rows/Columns

Now that I have covered some basic VBA macro examples, let me give you some useful macro codes involving cells. rows, or columns that you can use in your day-to-day work.

Hide Rows Based on Values/Text In It

Sub HideRowsBasedOnText()
    Dim ws As Worksheet
    Dim cell As Range
    Dim targetRange As Range
    
    ' Initialize the worksheet object
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Define the target range to loop through
    Set targetRange = ws.Range("A1:A10")
    
    ' Loop through each cell in the target range
    For Each cell In targetRange
        'Check if the cell value contains the text "Excel"
        If cell.Value = "Excel" Then
            'Hide the entire row containing this cell
            cell.EntireRow.Hidden = True
        Else
            'Unhide the row if the condition is not met
            cell.EntireRow.Hidden = False
        End If
    Next cell
End Sub

In the above code, we loop through each cell in the specified range and use an If Then Statement to check if the cell value is “Excel”.

If the condition is met, that row is hidden; otherwise, it remains visible.

You can also use this same code with a slight modification to hide rows based on the numeric values in it.

For example, below is a VBA code that is going to go through each cell in range A1:A10, and hide rows where the value is less than 10.

Sub HideRowsBasedOnValue()
    Dim ws As Worksheet
    Dim cell As Range
    Dim targetRange As Range
    
    ' Initialize the worksheet object
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Define the target range to loop through
    Set targetRange = ws.Range("A1:A10")
    
    ' Start looping through each cell in the target range
    For Each cell In targetRange
        ' Check if the cell value is less than 10
        If cell.Value < 10 Then
            'Hide the entire row containing this cell
            cell.EntireRow.Hidden = True
        Else
            'Unhide the row if the condition is not met
            cell.EntireRow.Hidden = False
        End If
    Next cell
End Sub

Unhide Rows Based on Values/Text In It

Sub UnhideRowsBasedOnText()
    Dim ws As Worksheet
    Dim cell As Range
    Dim targetRange As Range
    
    ' Initialize the worksheet object
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Define the target range to loop through
    Set targetRange = ws.Range("A1:A10")
    
    ' Loop through each cell in the target range
    For Each cell In targetRange
        'Check if the cell value contains the text "Excel"
        If cell.Value = "Excel" Then
            'Unhide the entire row containing this cell
            cell.EntireRow.Hidden = False
        End If
    Next cell
End Sub

In the above code, we specify the target worksheet and the range where we want to apply our row-unhiding criteria.

Then, we loop through each cell in that range using the For Each Next loop. For each cell in the range, we check the value in the cell, and if the cell contains the text “Excel”, it will be unhidden.

Unmerge all Merged Cells

Sub UnmergeAllCells()
    Dim ws As Worksheet
    
    ' Initialize the worksheet object
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Unmerge all merged cells in the worksheet
    ws.Cells.UnMerge
End Sub

The above code goes through all the cells in Sheet1 and unmerges any cells that have been merged.

In case you have any text in those merged cells, it would be placed in the top left cell after the cells have been unmerged.

Also read: Merge or Unmerge Cells in Excel (Shortcut)

Highlighlight All Merged Cells

Sub HighlightMergedCells()
    Dim ws As Worksheet
    Dim cell As Range
    
    ' Set ws as the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Loop through all cells in the used range of the worksheet
    For Each cell In ws.UsedRange
        'Check if the cell is merged
        If cell.MergeCells Then
            'If merged, set the background color to yellow
            cell.Interior.Color = RGB(255, 255, 0)
        End If
    Next cell
End Sub

This VBA code does the following:

1. It sets the active worksheet as “Sheet1”. You can change this based on your worksheet name.
2. It then loops through all the cells in the used range of that worksheet.
3. For each cell, it checks if the cell is merged or not.
4. If the cell is merged, it highlights it with a yellow color. You can change the color by changing the RGB values in the code above.

Delete Blank Rows

Sub DeleteBlankRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set ws to the active worksheet
    Set ws = ActiveSheet
    
    ' Find the last row with data in the worksheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop from the last row to the first row
    For i = lastRow To 1 Step -1
        'If the entire row is empty, delete it
        If Application.CountA(ws.Rows(i)) = 0 Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

This VBA code accomplishes the following tasks:

1. It identifies the active worksheet and assigns it to the ws variable.
2. It finds the last row with data in the worksheet by checking column “A”. This row number is stored in lastRow.
3. The code then loops from the last row up to the first row of the worksheet.
4. For each row, it checks whether the entire row is empty (using the COUNTA function). If it’s empty and the COUNTA returns 0, it deletes that row.

Also read: Remove Blank Rows in Excel (5 Ways + VBA)

Insert New Row After Every Other Row

Sub InsertRowAfterEveryOtherRow()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set ws to the active worksheet
    Set ws = ActiveSheet
    
    ' Find the last row with data in the worksheet
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Loop from the last row towards the first, moving in steps of 1
    For i = lastRow To 1 Step -1
        ' Insert a new row after each row we visit
        ws.Rows(i + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next i
End Sub

Here’s what the code does:

1. It identifies the active worksheet and stores it in the variable ws.
2. It finds the last row with data in column “A” of the worksheet and stores this row number in the variable lastRow.
3. The code then loops from the last row to the first row
4. For each visited row, it inserts a new row right after it.

In case you want to insert a blank row after every other row in a selected range of cells, you can use the VBA code below:

Sub InsertRowAfterEveryOtherRowInRange()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim i As Long
    
    ' Set ws to the active worksheet
    Set ws = ActiveSheet
    
    ' Use the user's selected range
    Set rng = Selection
    
    ' Initialize i with the last row of the selected range
    i = rng.Row + rng.Rows.Count - 1
    
    ' Loop from the last row to the first row of the selected range, moving in steps of 1
    For i = i To rng.Row Step -1
        'Insert a new row after each row we visit within the selected range
        ws.Rows(i + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Next i
End Sub

Also read: Count Rows using VBA in Excel

Highlight Duplicates Cells in a Column

Sub HighlightDuplicateValues()
    Dim myRange As Range
    Dim myCell As Range
    
    ' Set myRange to the user's selected range
    Set myRange = Selection
    
    ' Loop through each cell in the selected range
    For Each myCell In myRange
        'Use CountIf function to find duplicates within the range
        If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
            'Highlight the cell with ColorIndex 36 (light green)
            myCell.Interior.Color = vbYellow
        End If
    Next myCell
End Sub

Here’s what this code does:

• It sets myRange to the range of cells you’ve selected in Excel.
• It then loops through each cell within that selected range.
• The WorksheetFunction.CountIf function counts how many times each cell value appears in the selected range.
• If the count is greater than 1 (indicating that it’s a duplicate), it highlights the cell with a yellow color (which is given by vbYellow)

Reset All Filters – Clears filters from all sheets

Sub ResetAllFilters()
    Dim ws As Worksheet
    
    ' Loop through each worksheet in the active workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the sheet has autofilters enabled
        If ws.AutoFilterMode Then
            ' Clear the autofilters
            ws.AutoFilterMode = False
        End If
    Next ws
End Sub

Here’s what this code does:

• It loops through each worksheet in the active workbook.
• If a worksheet has autofilters applied (AutoFilterMode = True), it removes those filters by setting AutoFilterMode to False.

Remove Formulas But Keep Data (Convert Formulas to Values)

Sub ConvertFormulasToValues()
    Dim cell As Range
    Dim selectedRange As Range
    
    ' Set selectedRange to the user's selected range
    Set selectedRange = Selection
    
    ' Loop through each cell in the selected range
    For Each cell In selectedRange
        ' Check if the cell has a formula
        If cell.HasFormula Then
            ' Replace the formula with its value
            cell.Value = cell.Value
        End If
    Next cell
End Sub

Here’s what this code does:

• It sets selectedRange to the range of cells that you’ve selected in Excel.
• The code then loops through each cell within that selected range.
• If a cell contains a formula (HasFormula = True), the code replaces the formula with its output value.

Extract URLs from Hyperlinks

Sub ExtractURLsFromHyperlinks()
    Dim cell As Range
    Dim selectedRange As Range
    Dim hyperlinkAddress As String
    
    ' Set selectedRange to the user's selected range
    Set selectedRange = Selection
    
    ' Loop through each cell in the selected range
    For Each cell In selectedRange
        ' Check if the cell contains a hyperlink
        If cell.Hyperlinks.Count > 0 Then
            ' Get the URL of the first hyperlink in the cell
            hyperlinkAddress = cell.Hyperlinks(1).Address
            ' Write the URL to the adjacent cell in the next column
            cell.Offset(0, 1).Value = hyperlinkAddress
        End If
    Next cell
End Sub

Here’s how this code works:

• The selectedRange variable is set to the range of cells you’ve selected in Excel.
• The code then loops through each cell within that selected range.
• For each cell that contains a hyperlink, the URL (hyperlinkAddress) is extracted.
• The URL is then written into the adjacent cell in the column to the right.

Also read: How to Extract URL from Hyperlinks in Excel (Using VBA Formula)

Flip Data in Column

Sub FlipDataInColumn()
    Dim selectedRange As Range
    Dim dataArr() As Variant
    Dim i As Long, j As Long
    
    ' Set selectedRange to the user's selected range
    Set selectedRange = Selection
    j = selectedRange.Rows.Count
    
    ' Load the data into an array
    dataArr = selectedRange.Value
    
    ' Loop through the array and swap values to flip the data
    For i = 1 To j / 2
        temp = dataArr(i, 1)
        dataArr(i, 1) = dataArr(j, 1)
        dataArr(j, 1) = temp
        j = j - 1
    Next i
    
    ' Write the flipped data back to the column
    selectedRange.Value = dataArr
End Sub

Here’s what this code does:

• The selectedRange variable captures the range you’ve selected in Excel.
• The data in that selected range is stored in an array called dataArr.
• The code loops through the array and swaps the values from the top and bottom, working its way toward the middle, effectively reversing the data.
• Finally, the reversed data is written back to the original column.

Also read: How to Flip Data in Excel (Columns, Rows, Tables)?

Swap Cells

Sub SwapTwoCells()
    Dim cell1 As Range, cell2 As Range
    Dim temp As Variant
        
    ' Set cell1 and cell2 to the selected cells
    Set cell1 = Range("A1")
    Set cell2 = Range("A2")
    
    ' Store the value of cell1 in temp variable
    temp = cell1.Value
    
    ' Swap the values
    cell1.Value = cell2.Value
    cell2.Value = temp
End Sub

Here’s what this code accomplishes:

• It sets Range(“A1”) to cell1 and set Range(“A2”) to cell2
• The value of cell1 is stored temporarily in a variable named temp.
• Finally, the values of cell1 and cell2 are swapped.

Note: In this vb code, I have hardcoded the cells to be A1 and A2. You can change the cell references according your needs.

Swap Two Columns

Sub SwapTwoColumns()
    Dim colA As Range, colC As Range
    Dim temp As Variant
    
    ' Set colA and colC to columns A and C
    Set colA = Worksheets("Sheet1").Columns("A")
    Set colC = Worksheets("Sheet1").Columns("C")
    
    ' Store the data from colA in a temporary variable
    temp = colA.Value
    
    ' Perform the swap
    colA.Value = colC.Value
    colC.Value = temp
End Sub

Here’s what this code does:

• It sets colA to represent Column A and colC to represent Column C in “Sheet1” of the workbook.
• The values in colA are then temporarily stored in a variable named temp.
• Finally, the values in colA and colC are swapped using the temporary variable.

Auto Fit Columns

Sub AutoFitColumns()
    ' Auto-fit all columns in the active worksheet
    ActiveSheet.Columns.AutoFit
End Sub

The above VBA macro code uses the AutoFit method on the Columns object of the ActiveSheet to automatically adjust the width of all columns based on their content.

Also read: Autofit Column Width in Excel (Shortcut)

Auto Fit Rows

Sub AutoFitRows()
    ' Auto-fit all rows in the active worksheet
    ActiveSheet.Rows.AutoFit
End Sub

The above VBA macro code uses the AutoFit method on the rows object of the ActiveSheet to automatically adjust the width of all rows based on their content.

Make All Cells Same Size

Sub MakeCellsSameSize()
    ' Define default width and height
    Const defaultWidth As Double = 10
    Const defaultHeight As Double = 15
    
    ' Set all columns to the default width
    ActiveSheet.Columns.ColumnWidth = defaultWidth
    
    ' Set all rows to the default height
    ActiveSheet.Rows.RowHeight = defaultHeight
End Sub

Here is how this code works:

• It defines constants for the default width (defaultWidth) and height (defaultHeight) you want for each cell.
• Then, it sets the ColumnWidth of all Columns and the RowHeight of all Rows in the ActiveSheet to these default values.

Insert a New Column and Add Serial Numbers

Sub InsertColumnAndAddSerialNumbers()
    Dim lastRow As Long
    Dim i As Long
    
    ' Insert a new column at the beginning (Column A)
    Columns("A:A").Insert Shift:=xlToLeft, CopyOrigin:=xlFormatFromLeftOrAbove
    
    ' Find the last row with data in the worksheet
    lastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    ' Loop to populate serial numbers from 1 to lastRow
    For i = 1 To lastRow
        Cells(i, 1).Value = i
    Next i
End Sub

Here is how this VBA macro code works:

• It inserts a new column at the beginning of the active worksheet. This will become Column A, and existing columns will be shifted to the right.
• It finds the last row with data in Column B to determine how many serial numbers to create.
• It then fills the new Column A with serial numbers, starting from 1 and going up to the last row with data in Column B.

Also read: How to Convert Serial Numbers to Date in Excel

Insert Multiple Columns

Sub InsertMultipleColumns()
    Dim numberOfColumns As Integer
    
    ' Specify the number of columns to insert
    numberOfColumns = 5
    
    ' Insert multiple columns starting from Column D
    Columns("D:D").Resize(, numberOfColumns).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Here is how this VBA macro code works:

• It specifies the number of columns you wish to insert using the variable numberOfColumns. In this case, 5 columns will be inserted. You can change this value if you want.
• Then, it uses the Resize method to set the range to include as many columns as specified by numberOfColumns.
• Finally, the Insert method adds the columns starting from Column D, and existing columns are shifted to the right.

Insert Multiple Rows

Sub InsertMultipleRows()
    Dim numberOfRows As Integer
    Dim startRow As Integer
    
    ' Specify the number of rows to insert
    numberOfRows = 5
    
    ' Specify the starting row for insertion
    startRow = 3
    
    ' Insert multiple rows starting from row 3
    Rows(startRow & ":" & startRow + numberOfRows - 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Here is how this VBA macro code works:

• The numberOfRows variable specifies how many rows you want to insert. In this example, 5 rows will be inserted. You can change this value if you want to insert any other number of rows
• The startRow variable specifies the starting row for the insertion. In this example, row 3 is the starting row.
• The Rows object, combined with the Insert method, inserts the specified number of rows starting from startRow.

Protect all the Cells With Formulas

Sub ProtectCellsWithFormulas()
    Dim cell As Range
    Dim ws As Worksheet
    
    ' Set reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Unprotect the worksheet
    ws.Unprotect
    
    ' Loop through all cells in the used range
    For Each cell In ws.UsedRange
        ' Unlock all cells
        cell.Locked = False
        
        ' If the cell contains a formula, lock it
        If cell.HasFormula Then
            cell.Locked = True
        End If
    Next cell
    
    ' Protect the worksheet with locked cells
    ws.Protect
End Sub

Here is how this VBA macro code works:

• The above VBA code starts by setting a reference to the active worksheet and unprotecting it (in the possibility that it may be protected).
• It then loops through each cell in the used range of the worksheet, unlocking all cells first.
• If a cell contains a formula, it locks that cell.
• Finally, it protects the worksheet, which will make all locked cells uneditable while allowing changes to unlocked cells.

Unhide all Rows and Columns

Sub UnhideAllRowsAndColumns()
    ' Unhide all rows in the active worksheet
    ActiveSheet.Rows.Hidden = False
    
    ' Unhide all columns in the active worksheet
    ActiveSheet.Columns.Hidden = False
End Sub

The above VBA code sets the Hidden property of all Rows and Columns in the ActiveSheet to False. This effectively unhides all rows and columns in the worksheet.

Sort Data in One Column

Sub SortColumnAlphabetically()
    Dim ws As Worksheet
    Dim lastRow As Long
    
    ' Set a reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Find the last row with data in Column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Sort data in Column A from A1 to the last row with data
    ws.Range("A1:A" & lastRow).Sort Key1:=ws.Range("A1"), Order1:=xlAscending, Header:=xlNo
End Sub

Here is how this VBA macro code works:

• It sets a reference to the active worksheet and finds the last row with data in Column A.
• Then, it sorts the range from cell A1 to the last row in Column A in ascending alphabetical order.

If you want the sorting to be done in a descending alphabetical order, you can change Order1:=xlDescending

If you want to sort only the selected range of cells, you can use the below code:

Sub SortSelectedRangeAlphabetically()
    Dim ws As Worksheet
    Dim selectedRange As Range
    
    ' Set a reference to the active worksheet and selected range
    Set ws = ActiveSheet
    Set selectedRange = Selection
    
    ' Sort the selected range alphabetically in ascending order
    selectedRange.Sort Key1:=selectedRange.Cells(1, 1), Order1:=xlAscending, Header:=xlNo
End Sub

Sort Dataset Based on One Column Values

Sub SortDatasetBasedOnOneColumn()
    Dim ws As Worksheet
    Dim lastRow As Long
    
    ' Set a reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Find the last row with data in Column B
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Sort the dataset based on Column B values
    ws.Range("A1:C" & lastRow).Sort Key1:=ws.Range("B1:B" & lastRow), Order1:=xlAscending, Header:=xlNo
End Sub

Here is how this VBA macro code works:

• It sets a reference to the active worksheet and finds the last row with data in Column B.
• It sorts the entire dataset, which is in the range A1:C10, based on the values in Column B in ascending order.

In case you want to sort the entire dataset that you have already selected based on one column, you can use the code below.

Sub SortSelectedDatasetBasedOnOneColumn()
    Dim ws As Worksheet
    Dim selectedRange As Range
    Dim keyColumn As Range
    
    ' Set a reference to the active worksheet and selected range
    Set ws = ActiveSheet
    Set selectedRange = Selection
    
    ' Set the key column for sorting as the first column in the selected range
    Set keyColumn = selectedRange.Columns(1)
    
    ' Sort the selected dataset based on the key column
    selectedRange.Sort Key1:=keyColumn, Order1:=xlAscending, Header:=xlNo
End Sub

Note: The same code can also be used in case you want to sort your data based on numbers instead of alphabetically

Sort Data by Multiple Columns

Sub SortSelectedDataByMultipleColumns()
    Dim ws As Worksheet
    Dim selectedRange As Range
    Dim keyColumn1 As Range
    Dim keyColumn2 As Range
    
    ' Set a reference to the active worksheet and selected range
    Set ws = ActiveSheet
    Set selectedRange = Selection
    
    ' Set key columns for sorting within the selected range
    Set keyColumn1 = selectedRange.Columns(1)
    Set keyColumn2 = selectedRange.Columns(2)
    
    ' Sort the selected dataset based on multiple key columns
    selectedRange.Sort Key1:=keyColumn1, Order1:=xlAscending, _
                       Key2:=keyColumn2, Order2:=xlDescending, Header:=xlNo
End Sub

Here is how this VBA macro code works:

• It establishes a reference to the active worksheet and identifies the range currently selected by the user. The selected range is then set to the variable selectedRange
• It sets the first and second columns within the selected range as the key columns for sorting. You can change these based on your data set
• The selected dataset is sorted first by the values in the first column in ascending order and then by the values in the second column in descending order.

Replace Blank Cells with Zeros

Sub ReplaceBlanksWithZeros()
    Dim ws As Worksheet
    Dim selectedRange As Range
    Dim cell As Range
    
    ' Set a reference to the active worksheet and selected range
    Set ws = ActiveSheet
    Set selectedRange = Selection
    
    ' Loop through each cell in the selected range
    For Each cell In selectedRange
        ' If the cell is empty, set its value to zero
        If IsEmpty(cell.Value) Then
            cell.Value = 0
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

• It sets a reference to the active worksheet and identifies the range currently selected by the user.
• It loops through each cell in the selected range to check if it is empty.
• If a cell is found to be empty, the code sets its value to zero.

Replace Blank Cells with NA or Any Other Text

Sub ReplaceBlanksWithNA()
    Dim ws As Worksheet
    Dim selectedRange As Range
    Dim cell As Range
    Dim replaceText As String
    
    ' Set the text you want to replace blank cells with
    replaceText = "NA"
    
    ' Set a reference to the active worksheet and selected range
    Set ws = ActiveSheet
    Set selectedRange = Selection
    
    ' Loop through each cell in the selected range
    For Each cell In selectedRange
        ' If the cell is empty, set its value to "NA"
        If IsEmpty(cell.Value) Then
            cell.Value = replaceText
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

• It sets the variable ws to the active sheet and the variable selectedRange to the current selection.
• It loops through each cell in the selected range to check if it’s empty.
• If a cell is found to be empty, it sets that cell’s value to “NA” or any text specified in the replaceText variable.

In this example, I have put the text “NA” In a blank cell. You can modify the VBA code to put any text you want.

Also read: VBA Type Mismatch Error (Error 13)

Worksheet Related VBA Macro Codes

Let’s now look at some VBA macro code examples that you can use to manage worksheets in your workbooks.

Sort Sheets Alphabetically

Sub SortSheetsAlphabetically()
    Dim i As Integer, j As Integer
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    ' Loop through each worksheet to sort them
    For i = 1 To ThisWorkbook.Sheets.Count
        For j = i + 1 To ThisWorkbook.Sheets.Count
            ' Set references to the worksheets to be compared
            Set ws1 = ThisWorkbook.Sheets(i)
            Set ws2 = ThisWorkbook.Sheets(j)
            
            ' Compare worksheet names and swap if necessary
            If ws1.Name > ws2.Name Then
                ws2.Move Before:=ws1
            End If
        Next j
    Next i
End Sub

Here is how this VBA macro code works:

• It loops through each worksheet in the active workbook.
• For each pair of worksheets, it compares their names.
• If the first worksheet’s name (in the pair) is greater than the second, it moves the second worksheet before the first.

Protect All Sheets

Sub ProtectAllSheets()
    Dim ws As Worksheet
    Dim pwd As String
    
    ' Set the password
    pwd = "password123"
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Protect the worksheet with the password
        ws.Protect Password:=pwd
    Next ws
End Sub

Here is how this VBA macro code works:

• It sets a password that will be used to protect all worksheets. You can change the password by modifying the pwd variable.
• It loops through each worksheet in the active workbook (using the For Next Loop).
• Within the loop, it protects each worksheet using the specified password.

If you want to protect the worksheets but not specify any password, you can use the VBA code below:

Sub ProtectAllSheetsNoPassword()
    Dim ws As Worksheet
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Protect the worksheet without a password
        ws.Protect
    Next ws
End Sub

UnProtect All Sheets

Sub UnprotectAllSheets()
    Dim ws As Worksheet
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Unprotect the worksheet
        ws.Unprotect
    Next ws
End Sub

Here is how this VBA macro code works:

• It loops through each worksheet in the active workbook.
• For each worksheet, it removes the protection.

Unhide all Hidden Worksheets

Sub UnhideAllSheets()
    Dim ws As Worksheet
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Unhide the worksheet if it is hidden
        If ws.Visible = xlSheetHidden Or ws.Visible = xlSheetVeryHidden Then
            ws.Visible = xlSheetVisible
        End If
    Next ws
End Sub

Here is how this VBA macro code works:

• It loops through each worksheet in the active workbook.
• For each worksheet, it checks whether the worksheet is hidden or very hidden.
• If a worksheet is hidden or very hidden, it changes the visibility status to visible.

Delete all but the Active Worksheet

Sub DeleteAllButActive()
    Dim ws As Worksheet
    
    ' Disable alerts to prevent confirmation boxes from appearing
    Application.DisplayAlerts = False
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' If the worksheet is not the active sheet, delete it
        If ws.Name <> ActiveSheet.Name Then
            ws.Delete
        End If
    Next ws
    
    ' Enable alerts
    Application.DisplayAlerts = True
End Sub

Here is how this VBA macro code works:

• It disables Excel’s warning messages to prevent confirmation boxes from appearing during the deletion process.
• It loops through each worksheet in the active workbook.
• For each worksheet, it checks whether it’s the active sheet. If it’s not, the worksheet is deleted.
• Finally, it re-enables Excel’s warning messages.

Remember that the changes done by VBA codes are irreversible. So, make sure you have a backup copy of the workbook.

Insert Multiple Worksheets

Sub InsertMultipleWorksheets()
    Dim i As Integer
    Dim ws As Worksheet
    
    ' Number of sheets to insert
    Dim numSheets As Integer
    numSheets = 5
    
    ' Loop to insert multiple sheets
    For i = 1 To numSheets
        ' Insert new worksheet at the end
        Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    Next i
End Sub

Here is how this VBA macro code works:

• It sets the number of new worksheets to insert; you can change this by modifying the numSheets variable.
• It then loops through based on the numsheets value, inserting a new worksheet each time.

Delete all Blank Worksheets (Remove Empty Sheets)

Sub DeleteBlankSheetsUsingCOUNTA()
    Dim ws As Worksheet
    Dim nonEmptyCells As Long
    
    ' Disable alerts to prevent confirmation boxes from appearing
    Application.DisplayAlerts = False
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Count non-empty cells in the worksheet using COUNTA function
        nonEmptyCells = Application.WorksheetFunction.CountA(ws.Cells)
        
        ' Delete the worksheet if COUNTA returns 0, meaning it's empty
        If nonEmptyCells = 0 Then
            ws.Delete
        End If
    Next ws
    
    ' Enable alerts
    Application.DisplayAlerts = True
End Sub

Here is how this VBA macro code works:

• It disables Excel’s warning messages to avoid confirmation boxes during the deletion process (by setting Application.DisplayAlerts = False).
• It loops through each worksheet in the workbook.
• It uses the COUNTA function to count non-empty cells for each sheet.
• If a worksheet has no non-empty cells (COUNTA returns 0), the sheet gets deleted.
• Finally, it re-enables Excel’s warning messages.

Send Active Sheet in Email

Sub SendActiveSheetInEmail()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim tempFilePath As String
    
    ' Create a new Outlook application
    Set OutlookApp = CreateObject("Outlook.Application")
    
    ' Create a new email item
    Set OutlookMail = OutlookApp.CreateItem(0)
    
    ' Save the active sheet to a temporary location
    tempFilePath = Environ("temp") & "\" & ActiveSheet.Name & ".xlsx"
    ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:=tempFilePath
    ActiveWorkbook.Close SaveChanges:=False
    
    ' Configure and send email
    With OutlookMail
        .To = "Excel@stevespreadsheetplanet.com"  ' Modify this line
        .Subject = "Latest Data Attached" ' Modify this line
        .Body = "Please find the attached the Latest Data." ' Modify this line
        .Attachments.Add tempFilePath ' Attach the temporary file
        .Send
    End With
    
    ' Delete the temporary file
    Kill tempFilePath
    
    ' Release the Outlook objects
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

Here is how this VBA macro code works:

• It sets up an Outlook application and email item, just like before.
• It saves the active sheet as a temporary Excel file.
• It configures the email with recipient, subject, and body information and attaches the temporary Excel file.
• It displays the email for you to review.

If you would like to send the email (instead of Outlook showing you the email as display). Replace .Display with .Send in the above code.

Also read: VBA to Copy Range to Email Body

Change Sheet Tab Colors

Sub ChangeSheetTabColors()
    Dim ws As Worksheet
    Dim newColor As Long
    
    ' Define the new color (RGB value of Green)
    newColor = RGB(255, 0, 0)
    
    ' Loop through each worksheet in the active workbook
    For Each ws In ThisWorkbook.Sheets
        ' Change the tab color
        ws.Tab.Color = newColor
    Next ws
End Sub

Here is how this VBA macro code works:

• It defines the new tab color as green, using the RGB value (0, 255, 0).
• It then loops through each worksheet in the active workbook.
• For each worksheet, it changes the tab color to the defined new color (which is red in this code example).

Export Worksheets as CSV Files

Sub ExportWorksheetsAsCSV()
    Dim ws As Worksheet
    Dim folderPath As String
    Dim csvFilePath As String
    
    ' Get the folder path of the active workbook
    folderPath = ThisWorkbook.Path
    
    ' Loop through each worksheet in the active workbook
    For Each ws In ThisWorkbook.Sheets
        ' Generate the full path for the new CSV file
        csvFilePath = folderPath & "\" & ws.Name & ".csv"
        
        ' Export the worksheet to a CSV file
        ws.Copy
        ActiveWorkbook.SaveAs Filename:=csvFilePath, FileFormat:=xlCSV
        ActiveWorkbook.Close SaveChanges:=False
    Next ws
End Sub

Here is how this VBA macro code works:

• It gets the folder path of the active workbook. This is where the CSV files will be saved. You can modify the code if you want to save the CSV files in a different location.
• It loops through each worksheet in the active workbook.
• For each worksheet, it exports the content to a new CSV file. The CSV file will have the same name as the worksheet and will be saved in the same directory as the workbook.

Also read: How to Convert a CSV File to Excel?

Workbook Related VBA Macro Codes

In this section, I have some useful Workbook related VBA macro code examples.

Backup Current Workbook With TimeStamp

Sub BackupWorkbookWithTimestamp()
    Dim filePath As String
    Dim fileName As String
    Dim timeStamp As String
    
    ' Create a timestamp in the format "yyyyMMdd_hhmmss"
    timeStamp = Format(Now, "yyyyMMdd_hhmmss")
    
    ' Get the current workbook's path and name
    filePath = ThisWorkbook.Path
    fileName = ThisWorkbook.Name
    
    ' Create the new name for the backup file by appending the timestamp
    Dim backupFileName As String
    backupFileName = Replace(fileName, ".xlsx", "_" & timeStamp & ".xlsx")
    
    ' Generate the full path for the backup file
    Dim backupFilePath As String
    backupFilePath = filePath & "\" & backupFileName
    
    ' Save a backup copy of the current workbook
    ThisWorkbook.SaveCopyAs backupFilePath
End Sub

Here is how this VBA macro code works:

• It generates a timestamp in the format yyyyMMdd_hhmmss, using the current date and time.
• It fetches the path and filename of the current workbook.
• It creates a new filename for the backup, which incorporates the timestamp.
• Finally, it saves a backup copy of the workbook at the specified path, using the new filename.

Copy Active Sheet into a New Workbook

Sub CopyActiveSheetToNewWorkbook()
    Dim newWb As Workbook
    Dim ws As Worksheet
    
    ' Reference the active worksheet
    Set ws = ActiveSheet
    
    ' Create a new workbook
    Set newWb = Workbooks.Add
    
    ' Copy the active sheet to the new workbook
    ws.Copy Before:=newWb.Sheets(1)
    
    ' Delete the default sheet created when the new workbook was made
    Application.DisplayAlerts = False
    newWb.Sheets(2).Delete
    Application.DisplayAlerts = True
End Sub

Here is how this VBA macro code works:

• It references the active worksheet in the current workbook.
• It creates a new workbook.
• The active worksheet is then copied into this new workbook.
• Finally, the default worksheet that was created when the new workbook was added is deleted.

Send Active Workbook in Email

Sub SendActiveWorkbookInEmail()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim wb As Workbook
    
    ' Reference to the active workbook
    Set wb = ThisWorkbook
    
    ' Create a new Outlook application
    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
    
    ' Save the workbook to ensure changes are sent
    wb.Save
    
    ' Configure and send the email
    With OutlookMail
        .To = "recipient@example.com" ' Replace with the recipient's email
        .CC = ""
        .BCC = ""
        .Subject = "Here is the workbook"
        .Body = "Please find attached the workbook."
        .Attachments.Add wb.FullName
        .Display ' Use .Send to send the email automatically
    End With
    
    ' Release resources
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

Here is how this VBA macro code works:

1. It sets a reference to the active workbook and a new Outlook application.
2. It saves the workbook to make sure the latest changes are included.
3. It then configures the email fields, such as the recipient, subject, and body, and attaches the workbook.
4. Finally, it displays the email for you to review.

Show Welcome Message on Opening Workbook

Here is how this VBA macro code works:

Show Closing Messaging Before Closing Workbook

Private Sub Workbook_Open()
    ' Display a welcome message box
    MsgBox "You're Awsome", vbInformation, "Welcome"
End Sub

Here is how this VBA macro code works:

1. It uses the Workbook_Open event, which is executed when the workbook is opened.
2. Inside this event, a message box is displayed with a welcome message.

Important Note: Since this is an event code, which means that it is run when an event happens (such as opening a workbook), you need to paste this code in the ThisWorkbook object code window. To do this, double-click on the ThisWorkbook object in the Properties pane and then put this code in the code window that opens.

Closes All Open Excel Workbooks (Except the Active One)

Sub CloseAllWorkbooksExceptActive()
    Dim wb As Workbook
    Dim activeWb As Workbook
    
    ' Store a reference to the active workbook
    Set activeWb = ActiveWorkbook
    
    ' Loop through each open workbook
    For Each wb In Application.Workbooks
        ' If the workbook is not the active one, close it
        If Not wb Is activeWb Then
            wb.Close SaveChanges:=False ' Change to True if you want to save changes
        End If
    Next wb
End Sub

Here is how this VBA macro code works:

1. It sets a reference to the active workbook so that it can be skipped while closing others.
2. Then, it loops through all open workbooks.
3. If a workbook is not the active one, it gets closed. The code is set to not save changes, but you can change that if you wish.

Formatting Related VBA Macro Codes

Now lets look at some formatting related VBA macro examples.

Remove Text Wrap

Sub RemoveTextWrapFromSheet()
    Dim ws As Worksheet
    
    ' Reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Remove text wrap from all cells in the worksheet
    ws.Cells.WrapText = False
End Sub

Here is how this VBA macro code works:

1. It sets a reference to the active worksheet.
2. It changes the WrapText property of all cells (ws.Cells) in the worksheet to False, effectively removing text wrap.

Clear Content from Sheet

Sub ClearSheetContent()
    Dim ws As Worksheet
    
    ' Reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Clear content from all cells in the worksheet
    ws.Cells.ClearContents
End Sub

Here is how this VBA macro code works:

1. It sets a reference to the active worksheet.
2. Then, it uses the ClearContents method on all the cells in the worksheet, effectively clearing all data while leaving formatting intact.

Highlight Negative Numbers

You can do this in two ways:

• Using VBA code to apply conditional formatting to all cells
• Using VBA to go through each cell and highlight ones that have negative values

If you want the highlighting to be dynamic, you can use the conditional formatting code, and if you want to just highlight all the cells in one go (and don’t want it to be dynamic), you can use the second method.

Below is the code that uses Conditional Formatting:

Sub HighlightNegativeNumbers()
    Dim ws As Worksheet
    Dim condFormat As FormatCondition
    
    ' Reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Clear any existing conditional formatting
    ws.Cells.FormatConditions.Delete
    
    ' Add new conditional formatting rule to highlight negative numbers
    Set condFormat = ws.Cells.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLess, Formula1:="0")
    
    ' Set the formatting options for negative numbers
    With condFormat.Interior
        .ColorIndex = 3  ' Red
    End With
End Sub

Here is how this VBA macro code works:

1. It sets a reference to the active worksheet.
2. It clears any existing conditional formatting from all cells.
3. It then adds a new conditional formatting rule to highlight cells with negative numbers.

Below is the code that does not uses Conditional Formatting:

Sub HighlightNegativeNumbersNoCondFormat()
    Dim ws As Worksheet
    Dim cell As Range
    
    ' Set a reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Loop through each cell in the used range of the worksheet
    For Each cell In ws.UsedRange.Cells
        ' Check if the cell value is negative
        If cell.Value < 0 Then
            ' Highlight the cell in red (ColorIndex 3)
            cell.Interior.ColorIndex = 3
        End If
    Next cell
End Sub

In the above code, I have defined ws to represent the active worksheet. Then, I loop through each cell in the UsedRange of that worksheet. Inside the loop, the script checks if the cell value is negative. If it is, the cell’s background is set to red.

Highlight Cells Specific Text

Sub HighlightSpecificText()
    Dim ws As Worksheet
    Dim cell As Range
    Dim specificText As String
    
    ' Set specific text to look for
    specificText = "Excel"
    
    ' Reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Loop through each cell in the used range
    For Each cell In ws.UsedRange.Cells
        ' Check if the cell contains the specific text
        If InStr(1, cell.Value, specificText, vbTextCompare) > 0 Then
            ' Highlight the cell in yellow (ColorIndex 6)
            cell.Interior.ColorIndex = 6
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. It sets a specific text string that we want to search for in each cell. Here, it’s set as “Excel”. You can change this text in the code.
2. It references the active worksheet.
3. The script loops through each cell in the UsedRange of that worksheet.
4. Inside the loop, it uses the InStr function to see if the cell contains the specific text.
5. If the cell contains the specific text, it highlights the cell in yellow.

Highlight Cells with Comments

Sub HighlightCellsWithComments()
    Dim ws As Worksheet
    Dim cell As Range
    
    ' Reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Loop through each cell in the used range
    For Each cell In ws.UsedRange
        ' Check if the cell has a comment
        If Not cell.Comment Is Nothing Then
            ' Highlight the cell in yellow (ColorIndex 6)
            cell.Interior.ColorIndex = 6
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. It sets a reference to the active worksheet.
2. It then iterates through each cell in the UsedRange of the worksheet.
3. Inside the loop, it checks whether the cell contains a comment using the cell.Comment Is Nothing check.
4. If a comment is present, the cell is highlighted in yellow.

Highlight Alternate Rows in the Selection

Sub HighlightAlternateRowsInSelection()
    Dim cell As Range
    Dim i As Long
    
    ' Initialize the row counter
    i = 0
    
    ' Loop through each cell in the first column of the selection
    For Each cell In Selection.Columns(1).Cells
        ' Increment the row counter
        i = i + 1
        
        ' Check if the row is odd or even
        If i Mod 2 = 0 Then
            ' Highlight the entire row within the selection in light blue (ColorIndex 34)
            cell.EntireRow.Interior.ColorIndex = 6
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. It initializes a row counter i to keep track of the row number within the loop.
2. It loops through each cell in the first column of the selection, so you only go through each row once.
3. It checks whether the row counter is odd or even using the Mod function.
4. If the row is even, it highlights the row in yellow.

Highlight Cells with Misspelled Words

Sub HighlightMisspelledCells()
    Dim rng As Range  ' Declare a variable to hold each cell in the UsedRange
    
    ' Loop through each cell in the UsedRange of the active sheet
    For Each rng In ActiveSheet.UsedRange
        ' Use the Application.CheckSpelling function to check the spelling of the cell's text
        If Not Application.CheckSpelling(word:=rng.Text) Then
            ' If the word is misspelled, apply the "Bad" style to the cell
            rng.Interior.ColorIndex = 6
        End If
    Next rng
End Sub

Here is how this VBA macro code works:

1. It loops through each cell in the UsedRange of the active sheet.
2. It uses the Application.CheckSpelling function to check the spelling of each cell’s content.
3. If a misspelling is detected, it applies a yellow color to the cell.

Highlight Cells with a Specific Text in Worksheet

Sub HighlightErrorCells()
    Dim ws As Worksheet
    Dim cell As Range
    
    ' Reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Loop through each cell in the used range of the worksheet
    For Each cell In ws.UsedRange
        ' Check if the cell contains an error
        If IsError(cell.Value) Then
            ' Highlight the cell in red (ColorIndex 3)
            cell.Interior.ColorIndex = 3
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. It loops through each cell in the UsedRange of the active sheet.
2. IsError function is used to determine whether the cell contains an error. This function returns True for Excel’s standard error types (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
3. If the cell contains an error, its background color is set to red using cell.Interior.ColorIndex = 3.

Highlight Max Value In One or Multiple Column

Sub HighlightMaxValue()
    Dim cell As Range
    Dim maxVal As Double
    
    ' Use Excel's MAX function to find the maximum value in the selected range
    maxVal = Application.WorksheetFunction.Max(Selection)
    
    ' Loop through each cell in the selected range to highlight the maximum value
    For Each cell In Selection
        If cell.Value = maxVal Then
            cell.Interior.ColorIndex = 6  ' Highlight with yellow color
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. The Application.WorksheetFunction.Max function is used to find the maximum value within the selected range.
2. A For Each Next loop then iterates through the selected range to highlight the cell(s) with the maximum value.

In case your selection involves multiple columns and you want to highlight max value in each column, you can use the code below:

Sub HighlightMaxValueInEachColumn()
    Dim cell As Range
    Dim col As Range
    Dim maxVal As Double
    
    ' Loop through each column in the selected range
    For Each col In Selection.Columns
        ' Use Excel's MAX function to find the maximum value in the current column
        maxVal = Application.WorksheetFunction.Max(col)
        
        ' Loop through each cell in the current column to highlight the maximum value
        For Each cell In col.Cells
            If cell.Value = maxVal Then
                cell.Interior.ColorIndex = 6  ' Highlight with yellow color
            End If
        Next cell
    Next col
End Sub

Highlight Min Value In One or Multiple Column

Sub HighlightMinValue()
    Dim cell As Range
    Dim minVal As Double
    
    ' Use Excel's MIN function to find the maximum value in the selected range
    minVal = Application.WorksheetFunction.Min(Selection)
    
    ' Loop through each cell in the selected range to highlight the maximum value
    For Each cell In Selection
        If cell.Value = minVal Then
            cell.Interior.ColorIndex = 3  ' Highlight with red color
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. The Application.WorksheetFunction.Min function is used to find the minimum value within the selected range.
2. A For Next loop goes through each cell in the selected range to highlight the cell(s) with the minimum value.

In case your selection involves multiple columns and you want to highlight the minimum value in each column, you can use the code below:

Sub HighlightMinValueInEachColumn()
    Dim cell As Range
    Dim col As Range
    Dim minVal As Double
    
    ' Loop through each column in the selected range
    For Each col In Selection.Columns
        ' Use Excel's Min function to find the minimum value in the current column
        minVal = Application.WorksheetFunction.Min(col)
        
        ' Loop through each cell in the current column to highlight the maximum value
        For Each cell In col.Cells
            If cell.Value = minVal Then
                cell.Interior.ColorIndex = 3  ' Highlight with red color
            End If
        Next cell
    Next col
End Sub

Remove Gridlines in Sheet/Workbook

Sub RemoveGridlinesFromActiveSheet()
    ' Set the DisplayGridlines property to False for the active sheet
    ActiveWindow.DisplayGridlines = False
End Sub

And if you want to remove gridlines from all sheets in the workbook, you can use the following code:

Sub RemoveGridlinesFromAllSheets()
    Dim ws As Worksheet
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Activate the worksheet
        ws.Activate
        ' Set the DisplayGridlines property to False
        ActiveWindow.DisplayGridlines = False
    Next ws
End Sub

Auto-Resize Comment Boxes

Sub AutoResizeCommentBoxes()
    Dim cmt As Comment
    Dim ws As Worksheet
    
    ' Reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Loop through each comment in the worksheet
    For Each cmt In ws.Comments
        ' Adjust the size of the comment box
        With cmt.Shape
            .AutoShapeType = msoShapeRectangle
            .TextFrame.AutoSize = True
        End With
    Next cmt
End Sub

Here is how this VBA macro code works:

1. Dim cmt As Comment declares a Comment object, and Dim ws As Worksheet declares a Worksheet object.
2. Set ws = ActiveSheet sets ws to refer to the currently active sheet.
3. The For Each cmt In ws.Comments loop iterates through all comments in the worksheet.
4. Inside the loop, .AutoShapeType = msoShapeRectangle sets the shape type of the comment box, and .TextFrame.AutoSize = True automatically resizes it.

Formula-Related VBA Macro Codes

Multiply all the Values by a Number

Sub MultiplyValuesByNumber()
    Dim cell As Range
    Dim multiplier As Double
    
    ' Set the multiplier value
    multiplier = 2 ' Replace this with the number by which you want to multiply
    
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains a numeric value
        If IsNumeric(cell.Value) Then
            ' Multiply the cell value by the multiplier
            cell.Value = cell.Value * multiplier
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

• It loops through each cell in the Selection of the active sheet.
• IsNumeric function is used to determine whether the cell contains a numeric value. This function returns True if the cell contains a number and False otherwise.
• If the cell contains a numeric value, its value is multiplied by the specified multiplier using cell.Value = cell.Value * multiplier.

Remove Extra Spaces from Cells

Sub RemoveExtraSpaces()
    Dim cell As Range
    
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains a string value
        If cell.HasFormula = False And IsEmpty(cell.Value) = False Then
            ' Remove extra spaces
            cell.Value = WorksheetFunction.Trim(cell.Value)
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

• It loops through each cell in the Selection of the active sheet.
• cell.HasFormula = False And IsEmpty(cell.Value) = False is used to determine whether the cell contains a string value and is not empty or a formula. These conditions return True only if the cell contains text and is not empty or a formula.
• If the cell meets the conditions, extra spaces are removed using the WorksheetFunction.Trim(cell.Value). This Trim function removes all extra spaces in a string except for single spaces between words.

Reverse Text

Sub ReverseText()
    Dim cell As Range
    Dim str As String
    
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If cell.HasFormula = False And IsEmpty(cell.Value) = False Then
            ' Reverse the text in the cell
            str = cell.Value
            cell.Value = StrReverse(str)
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. It loops through each cell in the Selection of the active sheet.
2. cell.HasFormula = False And IsEmpty(cell.Value) = False is used to identify if the cell contains a text value and is not empty or a formula. These conditions return True if the cell contains text that’s neither empty nor a formula.
3. If the cell meets the criteria, its text is reversed using the StrReverse(str) function. This reverses the string held in the variable str, which contains the original cell value.

Extract Numbers from Text

Here is a VBA code that extracts all the numbers from the cells in the column adjacent to the column containing text and numbers. You can select the text column and run the code.

Sub ExtractNumbers()
    Dim cell As Range
    Dim str As String
    Dim numStr As String
    Dim i As Integer
    
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Initialize numStr to an empty string
        numStr = ""
        
        ' Retrieve the text from the cell
        str = cell.Value
        
        ' Loop through each character in the string
        For i = 1 To Len(str)
            ' Check if the character is numeric
            If IsNumeric(Mid(str, i, 1)) Then
                ' Add the numeric character to numStr
                numStr = numStr & Mid(str, i, 1)
            End If
        Next i
        
        ' Place the extracted numbers in the adjacent column (one column to the right)
        cell.Offset(0, 1).Value = numStr
    Next cell
End Sub

Here is how this VBA macro code works:

1. The macro loops through each cell in the selected range of the active sheet.
2. For each cell, it initializes numStr to an empty string and retrieves the cell’s content into the variable str.
3. The macro then uses a For loop to go through each character in str. If the character is numeric, it adds it to numStr.
4. Finally, the extracted numbers stored in numStr are placed in the adjacent column using cell.Offset(0, 1).Value.

Convert to Upper Case

Sub ConvertToUpperCase()
    Dim cell As Range
    
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text and is not empty
        If cell.HasFormula = False And IsEmpty(cell.Value) = False Then
            ' Convert the text in the cell to uppercase
            cell.Value = UCase(cell.Value)
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. The macro loops through each cell in the selected range of the active worksheet.
2. An If statement checks if the cell contains text and is not empty, ensuring we’re not modifying empty cells or cells with formulas.
3. If the cell meets these conditions, the text is converted to uppercase using the UCase() function.

Convert to Lower Case

Sub ConvertToLowerCase()
    Dim cell As Range
    
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text and is not empty
        If cell.HasFormula = False And IsEmpty(cell.Value) = False Then
            ' Convert the text in the cell to lowercase
            cell.Value = LCase(cell.Value)
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. The macro loops through each cell in the selected range of the active sheet.
2. An If statement checks if the cell contains text and is not empty, so we avoid modifying empty cells or cells with formulas.
3. If the cell meets the criteria, it uses the LCase() function to convert the text in the cell to lowercase.

Convert to Proper Case

Sub ConvertToProperCase()
    Dim cell As Range
    
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text and is not empty
        If cell.HasFormula = False And IsEmpty(cell.Value) = False Then
            ' Convert the text in the cell to proper case
            cell.Value = WorksheetFunction.Proper(cell.Value)
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. The macro loops through each cell in the selected range of the active worksheet.
2. It checks whether each cell contains text and is not empty, using an If statement. This way, we don’t modify empty cells or cells containing formulas.
3. If the cell meets these conditions, it uses the WorksheetFunction.Proper() function to convert the text in the cell to proper case.

Convert to Sentence Case

Sub ConvertToSentenceCase()
    Dim cell As Range
    Dim content As String
    Dim i As Long
    
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text and is not empty
        If cell.HasFormula = False And IsEmpty(cell.Value) = False Then
            content = LCase(cell.Value) ' Convert the entire string to lowercase
            ' Capitalize the first letter of the string
            Mid(content, 1, 1) = UCase(Mid(content, 1, 1))
            
            ' Capitalize the first letter after each period followed by a space
            For i = 1 To Len(content) - 2
                If Mid(content, i, 2) = ". " Then
                    Mid(content, i + 2, 1) = UCase(Mid(content, i + 2, 1))
                End If
            Next i
            
            ' Set the cell value to the converted string
            cell.Value = content
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. It loops through each cell in the selected range of the active sheet.
2. It checks whether each cell contains text and is not empty, so we avoid modifying empty cells or cells with formulas.
3. The string from each cell is first converted entirely to lowercase using LCase.
4. Then, the first letter and the first letter after every period followed by a space are capitalized using UCase.

Remove the Apostrophe from a Number

Sub RemoveApostropheFromNumbers()
    Dim cell As Range
    Dim cellValue As String
    
    ' Loop through each cell in the selection
    For Each cell In Selection
        ' Store the cell's value as a string
        cellValue = cell.Text
        
        ' Check if the first character is an apostrophe
        If Left(cellValue, 1) = "'" Then
            ' Remove the apostrophe and update the cell value
            cell.Value = Mid(cellValue, 2)
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

• The macro loops through each cell within the selected range.
• It checks if the first character in each cell is an apostrophe.
• If an apostrophe is found, it removes it and updates the cell’s value.

Remove Decimals from Numbers

Sub RemoveDecimalsFromNumbers()
    Dim cell As Range
    
    ' Loop through each cell in the selection
    For Each cell In Selection
        ' Check if the cell contains a numeric value
        If IsNumeric(cell.Value) Then
            ' Remove the decimal and update the cell value
            cell.Value = Round(cell.Value, 0)
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. The macro loops through each cell within the selected range.
2. It checks if the cell contains a numeric value.
3. If the cell contains a numeric value, the decimal is removed, and the cell value is updated (using the ROUND function).

Add/Subtract a Number in all the Numbers

Sub AddSubtractFromNumbers()
    Dim cell As Range
    Dim addValue As Double
    
    ' Define the value to add or subtract
    ' Change this to the value you want to add or subtract
    addValue = 5
    
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains a numeric value
        If IsNumeric(cell.Value) Then
            ' Add or subtract the specified value and update the cell
            cell.Value = cell.Value + addValue
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. addValue is the variable that has the number you want to add. You can change this to any number you like.
2. It loops through each cell in the selected range.
3. For each numeric cell, it adds (or subtracts, if addValue is negative) the number stored in addValue.

Remove Negative Signs

Sub RemoveNegativeSigns()
    Dim cell As Range
    
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains a numeric value
        If IsNumeric(cell.Value) Then
            ' Remove the negative sign by taking the absolute value
            cell.Value = Abs(cell.Value)
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. It loops through each cell in the selected range.
2. It checks if the cell contains a numeric value.
3. If the cell contains a numeric value, the code uses the Abs (absolute value) function to remove the negative sign and update the cell’s value.

Remove Text in Parenthesis

Sub RemoveTextInParentheses()
    Dim cell As Range
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    
    ' Regular Expression to find text in parentheses
    regex.Pattern = "\([^)]*\)"
    regex.Global = True
    
    ' Loop through each cell in the selected range
    For Each cell In Selection
        ' Check if the cell contains text
        If Not IsEmpty(cell.Value) Then
            ' Remove text in parentheses and update the cell
            cell.Value = regex.Replace(cell.Value, "")
        End If
    Next cell
End Sub

Here is how this VBA macro code works:

1. The regex object is set up to find all occurrences of text enclosed in parentheses.
2. The script loops through each cell in the selected range.
3. For each non-empty cell, it removes the text inside parentheses and updates the cell.

Generate Randon Numbers

Sub GenerateRandomNumbers()
    Dim i As Integer
    
    ' Initialize the random number generator
    Randomize
    
    ' Loop to generate 20 random numbers
    For i = 1 To 20
        ' Generate random numbers between 1 and 100
        ' Write them to cells in column A
        Cells(i, 1).Value = Int(Rnd * 100) + 1
    Next i
End Sub

Here is how this VBA macro code works:

1. Randomize is used to initialize the random number generator, ensuring that you get a different set of random numbers each time you run the code.
2. The For loop runs from 1 to 20 to generate 20 random numbers.
3. Inside the loop, the Rnd function generates a random number between 0 and 1. This is then multiplied by 100, rounded down using Int, and finally incremented by 1. This produces a random integer between 1 and 100.

Also read: How to Generate Random Names in Excel

Pivot Table Related VBA Macro Codes

Below are some Pivot Table related macro examples.

Hide Pivot Table Subtotals

Sub HidePivotTableSubtotals()
    Dim pt As PivotTable
    Dim pf As PivotField
    
    ' Set the PivotTable object to the first PivotTable in the active sheet
    Set pt = ActiveSheet.PivotTables(1)
    
    ' Loop through each PivotField in the PivotTable
    For Each pf In pt.PivotFields
        ' Hide subtotals for the PivotField
        pf.Subtotals(1) = False
    Next pf
End Sub

This VBA code works on the first PivotTable found in the active worksheet. It iterates through each field in that PivotTable and sets the Subtotals property to False. This action hides all types of subtotals for each field in the PivotTable.

Auto-Refresh Pivot Table

Sub AutoRefreshPivotTables()
    Dim pt As PivotTable
    Dim ws As Worksheet
    
    ' Set the worksheet object to the active sheet
    Set ws = ActiveSheet
    
    ' Loop through all Pivot Tables in the worksheet
    For Each pt In ws.PivotTables
        ' Refresh each Pivot Table
        pt.RefreshTable
    Next pt
End Sub

Here is how this VBA macro code works:

1. The variable pt is used to represent each individual PivotTable.
2. The variable ws stores the reference to the active worksheet.
3. A For Each loop iterates over all the PivotTables in the active worksheet and refreshes them one by one using pt.RefreshTable.

In case you want to refresh all the pivot tables in the workbook you can use the below VBA code example:

Sub AutoRefreshPivotTablesInWorkbook()
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim wb As Workbook
    
    ' Set the workbook object to the active workbook
    Set wb = ActiveWorkbook
    
    ' Loop through each worksheet in the workbook
    For Each ws In wb.Worksheets
        ' Loop through all Pivot Tables in each worksheet
        For Each pt In ws.PivotTables
            ' Refresh each Pivot Table
            pt.RefreshTable
        Next pt
    Next ws
End Sub

Charts Related VBA Macro Codes

Below are some charting related VBA codes that will save you time.

Delete All Charts – Removes all charts from the active sheet

Sub DeleteAllCharts()
    Dim chtObj As ChartObject
    Dim ws As Worksheet
    
    ' Reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Loop through all the chart objects in the worksheet
    For Each chtObj In ws.ChartObjects
        ' Delete each chart object
        chtObj.Delete
    Next chtObj
End Sub

Here is how this VBA macro code works:

1. The variable chtObj is used to represent each individual ChartObject.
2. The variable ws holds the reference to the active worksheet.
3. A For Each loop iterates through each ChartObject on the active worksheet and deletes it using the Delete method.

If you want to remove all the charts from the entire Excel workbook you can use the VBA code below:

Sub DeleteAllChartsInWorkbook()
    Dim ws As Worksheet
    Dim chtObj As ChartObject
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each chart object in the worksheet
        For Each chtObj In ws.ChartObjects
            ' Delete the chart object
            chtObj.Delete
        Next chtObj
    Next ws
End Sub

Make All Charts Same Size

Sub MakeAllChartsSameSize()
    Dim ws As Worksheet
    Dim chtObj As ChartObject
    Dim newWidth As Double, newHeight As Double
    
    ' Desired dimensions for all chart objects
    newWidth = 300
    newHeight = 200
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each chart object in the worksheet
        For Each chtObj In ws.ChartObjects
            ' Resize the chart object
            chtObj.Width = newWidth
            chtObj.Height = newHeight
        Next chtObj
    Next ws
End Sub

Here is how this VBA macro code works:

1. ws is used to hold each worksheet in the workbook.
2. chtObj is used to hold each individual ChartObject.
3. newWidth and newHeight are the new dimensions that you want to set for all the charts.
4. Two For Each loops are used: The outer loop iterates through each worksheet in the workbook, and the inner loop iterates through each chart object in the current worksheet, resizing it using the Width and Height properties.

Apply Border to All Charts

Sub ApplyBorderToAllCharts()
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim brd As Border
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each chart object in the worksheet
        For Each cht In ws.ChartObjects
            ' Apply a border to the chart area
            With cht.Chart.ChartArea.Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(0, 0, 0) ' Black color
                .Weight = 1 ' Weight of the line (can be adjusted)
            End With
        Next cht
    Next ws
End Sub

Here is how this VBA macro code works:

1. ws holds each worksheet in the workbook.
2. cht holds each individual ChartObject within a worksheet.
3. .ChartArea.Format.Line refers to the border of the chart area, where properties like visibility, color, and weight are set.

Convert all charts to images

Sub ConvertChartsToImages()
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim chtCounter As Long
    
    ' Initialize the chart counter
    chtCounter = 1
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each chart object in the worksheet
        For Each cht In ws.ChartObjects
            ' Export the chart to an image file (PNG format)
            cht.Chart.Export "C:\Temp\Chart" & chtCounter & ".png"
            ' Increment the chart counter
            chtCounter = chtCounter + 1
        Next cht
    Next ws
End Sub

Here is how this VBA macro code works:

1. ws represents each worksheet in the workbook.
2. cht represents each ChartObject within each worksheet.
3. chtCounter is a counter used for naming the exported image files.
4. cht.Chart.Export “C:\Temp\Chart” & chtCounter & “.png” exports each chart as a PNG image. The charts are saved to the “C:\Temp” directory with incremental filenames (e.g., Chart1.png, Chart2.png, etc.). You can change the file-saving location

Change Chart type

Sub ChangeAllChartsToLine()
    Dim ws As Worksheet
    Dim cht As ChartObject
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through each chart object in the worksheet
        For Each cht In ws.ChartObjects
            ' Change the chart type to Line Chart
            cht.Chart.ChartType = xlLine
        Next cht
    Next ws
End Sub

Here is how this VBA macro code works:

1. ws is a variable representing each worksheet in the workbook.
2. cht is a variable representing each ChartObject within each worksheet.
3. cht.Chart.ChartType = xlLine changes the chart type to a Line Chart.

Copy the formatting of one chart and apply it to another

Sub CopyChartFormat()
    Dim srcChart As Chart
    Dim destChart As Chart
    
    ' Set the source and destination charts
    Set srcChart = Sheets("Sheet1").ChartObjects("Chart 1").Chart
    Set destChart = Sheets("Sheet2").ChartObjects("Chart 2").Chart
    
    ' Copy the entire chart to copy formatting
    srcChart.Copy
    
    ' Paste the formatting to the destination chart
    destChart.Paste Type:=xlPasteFormats
    
End Sub

Here is how this VBA macro code works:

1. srcChart and destChart are variables representing the source and destination charts, respectively.
2. Sheets(“Sheet1”).ChartObjects(“Chart 1”).Chart identifies the source chart located in “Sheet1” named “Chart 1”.
3. Sheets(“Sheet2”).ChartObjects(“Chart 2”).Chart identifies the destination chart located in “Sheet2” named “Chart 2”.
4. srcChart.Copy copies the entire source chart, including its formatting.
5. destChart.Paste Type:=xlPasteFormats pastes only the formatting to the destination chart.

Print Related VBA Macro Codes

Below are some printing-related VBA macro code examples:

Print Comments

Sub PrintComments()
    Dim ws As Worksheet
    Set ws = ActiveSheet  ' Reference to the active worksheet
    
    ' Store the current print settings
    Dim originalSetting As XlPrintLocation
    originalSetting = ws.CommentsLayout
    
    ' Change the comments layout to print as end of sheet
    ws.CommentsLayout = xlCommentsPrintAsEndOfSheet
    
    ' Print the active worksheet
    ws.PrintOut
    
    ' Restore original print settings
    ws.CommentsLayout = originalSetting
End Sub

Here is how this VBA macro code works:

1. ws is a variable that stores a reference to the active worksheet.
2. originalSetting is used to store the original print setting of the worksheet so that it can be restored later.
3. ws.CommentsLayout = xlCommentsPrintAsEndOfSheet sets the comments layout to be printed at the end of the sheet.
4. ws.PrintOut prints the active worksheet along with the comments.
5. Finally, the original print settings are restored.

Print Selected Range

Sub PrintSelectedRange()
    Dim rng As Range  ' Declare a variable to hold the selected range
    
    ' Check if a range is selected
    If TypeName(Selection) = "Range" Then
        Set rng = Selection  ' Set the variable to the selected range
        
        ' Print the selected range
        rng.PrintOut
    Else
        MsgBox "Please select a range to print."
    End If
End Sub

Here is how this VBA macro code works:

1. A variable named rng is declared to hold the selected range.
2. A check is performed to ensure that the selected object is a range (If TypeName(Selection) = “Range” Then).
3. If a range is selected, the PrintOut method is used to print it (rng.PrintOut).
4. If something other than a range is selected, a message box prompts the user to select a range.

Some Advanced VBA Macro Codes

And finally, I have some advanced VBA macro code examples that you may want to try

Create a Table of Contents

Sub CreateTableOfContents()
    Dim ws As Worksheet
    Dim wsTOC As Worksheet
    Dim i As Integer
    
    ' Create a new worksheet for the Table of Contents
    Set wsTOC = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
    wsTOC.Name = "Table_of_Contents"
    
    ' Set the header for the Table of Contents
    wsTOC.Cells(1, 1).Value = "Table of Contents"
    wsTOC.Cells(1, 1).Font.Bold = True
    
    ' Initialize counter
    i = 2
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Skip the Table of Contents sheet
        If ws.Name <> "Table_of_Contents" Then
            ' Write the sheet name in the TOC
            wsTOC.Cells(i, 1).Value = ws.Name
            ' Create a hyperlink to the sheet
            wsTOC.Hyperlinks.Add _
                Anchor:=wsTOC.Cells(i, 1), _
                Address:="", _
                SubAddress:="'" & ws.Name & "'!A1", _
                TextToDisplay:=ws.Name
            ' Increment counter
            i = i + 1
        End If
    Next ws
End Sub

Here is how this VBA macro code works:

1. A new worksheet called “Table_of_Contents” is created at the beginning of the workbook.
2. A loop iterates through each worksheet in the workbook.
3. A hyperlink is created for each sheet, pointing to cell A1 of that sheet.
4. The sheet names, along with the hyperlinks, are listed on the “Table_of_Contents” worksheet.

Opens All Excel Files in a Specified Folder

Sub OpenAllExcelFilesInFolder()
    Dim folderPath As String
    Dim fileName As String
    Dim wb As Workbook
    
    ' Specify the folder path where the Excel files are stored
    folderPath = "C:\YourFolder\"
    
    ' Check if the folder path ends with a backslash, if not, add one
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    ' Get the first Excel file in the folder
    fileName = Dir(folderPath & "*.xls*")
    
    ' Loop through each Excel file in the folder
    Do While fileName <> ""
        ' Open the Excel file
        Set wb = Workbooks.Open(folderPath & fileName)
        
        ' Do something with the workbook (optional)
        
        ' Get the next Excel file in the folder
        fileName = Dir
    Loop
End Sub

Here is how this VBA macro code works:

1. The folderPath variable is where you specify the path to the folder containing the Excel files you want to open.
2. The Dir function is used to get the first Excel file in the folder.
3. A Do While loop is used to loop through each file in the folder.
4. Inside the loop, the Workbooks.Open method is used to open each Excel file.

Converts and Save Active Sheet as PDF

Sub SaveActiveSheetAsPDF()
    Dim ws As Worksheet
    Dim pdfFileName As String
    
    ' Reference to the active worksheet
    Set ws = ActiveSheet
    
    ' Define the PDF file name and path
    pdfFileName = "C:\YourFolder\" & ws.Name & ".pdf"
    
    ' Save the active sheet as a PDF
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, Quality:=xlQualityStandard, _
                            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Here is how this VBA macro code works:

• A reference to the active worksheet is set in the ws variable.
• The pdfFileName variable contains the path and the filename where the PDF will be saved. It uses the worksheet name for the filename.
• The ExportAsFixedFormat method saves the active worksheet as a PDF. Various options like quality, including document properties, and opening the PDF after publishing are set.

Save Each Worksheet as a Separate PDF

Sub SaveAllSheetsAsPDF()
    Dim ws As Worksheet
    Dim pdfFileName As String
    Dim pdfFolder As String
    
    ' Define the folder to save PDFs
    pdfFolder = "C:\YourFolder\"
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Sheets
        ' Generate PDF filename based on sheet name
        pdfFileName = pdfFolder & ws.Name & ".pdf"
        
        ' Export each sheet as PDF
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, _
                                Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next ws
End Sub

Here is how this VBA macro code works:

1. Defines the folder where the PDFs will be saved in the pdfFolder variable.
2. Loops through each worksheet in the active workbook.
3. Creates the full PDF file name, combining the folder path and the worksheet name.
4. Saves each worksheet as a PDF using ExportAsFixedFormat.

Converts and Save Workbook as PDF

Sub SaveWorkbookAsPDF()
    Dim pdfFileName As String
    Dim pdfFolder As String
    
    ' Define the folder to save the PDF
    pdfFolder = "C:\YourFolder\"
    
    ' Generate PDF filename based on the workbook name
    pdfFileName = pdfFolder & ThisWorkbook.Name & ".pdf"
    
    ' Export the entire workbook as a single PDF
    ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, _
                            Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                            IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

The code sets up a folder directory and a PDF filename, based on the workbook name. It then uses the ExportAsFixedFormat method to convert the entire workbook into a single PDF.

Replace “C:\YourFolder\” with the directory where you’d like to save the PDF. After running this macro, your active workbook will be saved as a PDF in the specified folder, containing all the sheets.

Save Selected Range as a PDF

Sub SaveRangeAsPDF()
    Dim pdfFileName As String
    Dim selectedRange As Range
    
    ' Define the folder to save the PDF
    pdfFileName = "C:\YourFolder\SelectedRange.pdf"
    
    ' Get the selected range
    Set selectedRange = Selection
    
    ' Export the selected range as PDF
    selectedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, _
                                       Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                       IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

In this code, a filename and directory are specified for the PDF. The ExportAsFixedFormat method is used to convert the selected range to a PDF.

Replace “C:\YourFolder\SelectedRange.pdf” with your desired file path and name. When you run this code, it will save the currently selected range of cells as a PDF in the folder you specify.

Resize All Images

Sub ResizeAllImages()
    Dim img As Picture
    Dim newWidth As Single, newHeight As Single
    
    ' Specify the new dimensions for images
    newWidth = 100
    newHeight = 100
    
    ' Loop through each picture on the active sheet
    For Each img In ActiveSheet.Pictures
        ' Resize the image to the new dimensions
        img.Width = newWidth
        img.Height = newHeight
    Next img
End Sub

The code identifies all the pictures in the active worksheet and resizes them to the specified dimensions. The dimensions are set using the newWidth and newHeight variables, and you can adjust these numbers according to your needs. The code then loops through each image and sets its dimensions to the ones specified.

So, these are more than 100 Excel VBA macro code examples that you can experiment with and use in your day-to-day work to save time and be more proficient.

Go ahead and bookmark this page, as I will keep updating these examples and add new VBA macro code examples.

Other Excel articles you may also like:

• Use Excel Shortcuts
• What is VBA in Excel?
• Macro vs. VBA
• VBA Macro Codes to Filter Data In Excel
• Microsoft Excel Terminology (Glossary)
• Excel VBA Runtime Error 1004
• VBA Runtime Error 91