VBA + Excel

VBA Code Examples


Excel Automation Tool

AutoMacro: VBA Add-in with Hundreds of Ready-To-Use VBA Code Examples & much more!


Search the list below for free Excel VBA code examples complete with explanations.
Some include downloadable files as well. These Excel VBA Macros & Scripts are professionally developed and ready-to-use.

We hope you find this list useful!

VBA Code Examples
In Depth Tutorialsyes
PowerPoint VBA Macro Examples & Tutorial
Sorting
Find and Find and Replace
Cheat Sheets – Commands & Syntax Lists
Word Macro Examples & VBA Tutorial
Basicsyes
If, ElseIf, Else (Ultimate Guide to If Statements)
Value Paste & PasteSpecial
VBA – Cut, Copy, Paste from a Macro
VBA Save File – 20 Easy Examples
VBA Coding Conceptsyes
Commenting in VBA
Creating (Custom) User Defined Functions (UDFs)
Excel VBA – Named Ranges and Programming
How to Rename or Delete a Module or Form
Opening & Using the Visual Basic Editor (VBE) in Excel
Return a Value from a Sub Procedure
Shortcuts for the Visual Basic Editor
Call Function from a Sub
Function – Call, Return Value, & Parameters
Object Model
On Error – Error Handling Best Practices
Private vs Public Procedures (Subs & Functions)
Select Case Statement
Sub Procedure
What is a VBA Module?
What is a VBA Project? (And How to Edit)
VBA – Language References
VBA – Modify the Right-Click Menu to Call a Macro
VBA – Run a Macro from a Macro
VBA – Run a Macro Line by Line
VBA – Wait, Delay, Create a Timer While Running Code
Debug.Print and Immediate Window
VBA DoEvents
VBA End
VBA Exit Sub or Function
VBA On Error Exit Sub
VBA Option Explicit
VBA: Improve Speed & Other Best Practices
Loopsyes
VBA For Loop – Loop Through a Range of Cells
Loops - Ultimate Guide
For Each Examples (Quick Reference)
VBA Exit For
VBA Exit Loop
While Wend Loop
Ranges and Cellsyes
Ranges and Cells - Ultimate Guide
Formulas - Ultimate Guide
Union and Intersect
Resize Range
VBA – Autofill with a Macro
VBA – Change a Cell;s Background Color
VBA – ClearFormats – Remove Formatting For Each Cell In Range
VBA – Conditional Formatting – Highlight Duplicates in a Range
VBA – Delete All Named Ranges
VBA – Delete Hyperlinks
VBA – Format Painter
VBA – Get the Active Cell’s Column or Row
VBA – Resize an Object to a Range Size
VBA – Select All Cells
VBA – Sum by Color
VBA – Test if Selection is a Range or Object
Cell Value – Get, Set, or Change
ClearContents / Clear Cells
Copy Destination (Copy Range to Another Sheet)
Current Region
Dynamic Range
Highlight Cell
VBA Merge Cells & Unmerge Cells
Offset Range or Cell
Range / Cell Address
Range.End (xlDown, xlUp, xlToRight, xlToLeft)
VBA Routine to return Column Letter of Cell
VBA Select Range or Cells
VBA Union – Join Multiple Ranges
VBA Wrap Text
Activecell Offset VBA
Rows & Columnsyes
Select (and work with) Entire Rows & Columns
Delete or Insert Rows Based on Cell Value
Delete Rows that Meet Certain Criteria in VBA
Hide / Unhide Columns & Rows
Unhide All Rows / Columns
VBA – Autofit Columns
VBA – Count the rows in a Selection
VBA – Delete Blank Rows
VBA – Select Every 5th Row
Copy / Paste Rows & Columns
Delete Entire Row or Column
Group Rows & Columns
Insert Row or Column
VBA Set Column Width or Row Height
VBA Used Range – Count Number of Used Rows or Columns
Sheetsyes
Sheets – The Ultimate Guide
VBA – Hide (or Unhide) a Worksheet
VBA – How to Use Worksheet Functions (and a Full List)
VBA – Loop Through all Worksheets with For Each
VBA – Macro to List all Sheets in a Workbook
Send Worksheets by Email as Separate Workbooks
VBA – Unhide All Worksheets
Clear Entire Sheet
VBA Copy Worksheet
VBA Delete or Clear Worksheet
Get Sheet Name / Rename Sheet
VBA Protect / Unprotect Worksheets
VBA Routine to Add and Name Worksheets
VBA Select Sheet, Activate Sheet, and Get Activesheet
Worksheet Range
VBA: Set the Default Sheet When a WorkBook Opens
How to Sort Tabs / Worksheets with a VBA Macro
Workbooksyes
Create New Workbook (Workbooks.Add)
Activate Workbook
ActiveWorkbook vs. ThisWorkbook
VBA Open / Close Workbook
Workbook Name (Get, Set, without Extension)
Workbook Protection (Password Protect / Unprotect)
Working with Workbooks (The Workbook Object)
Arraysyes
Arrays
VBA – Array Examples
Dictionary Objects
Collections
ArrayList
Array of Objects
Variant Array
Dynamic Array (Redim & Redim Preserve)
Declare & Initilize String Array
Declare (Dim), Create, and Initialize Array Variable
Array Length / Size
Assign Range to Array
Clear Array – Erase Function
Loop Through Array / For Each Item in Array
Sort Array
Transpose Array
Search for (Find) Value in Array
Populate Array with Unique Values from Column
Remove Duplicates From Array
Function Return Array
Multi-Dimensional Array (2D Arrays)
Output (Print) Array to Range
Errorsyes
Try Catch Errors
Object Required Error
Syntax Error VBA
Type Mismatch (Run-time Error 13)
Automation Error
Compile Error
Error 1004 – Application-Defined or Object-Defined Error
Ignore Error
On Error Resume Next or Goto 0
Out of Memory Error
Throw / Raise Error – Err.Raise – Custom Error Msg
Can’t Find Project or Library – VBA Compile Error
Compile Error in Hidden Module – VBA
Fix Error 400
Fix VBA Error 438 – Object Doesn’t Support Property or Method
Fix VBA Run Time Error 32809
Fix VBA Run Time Error 57121
Variablesyes
VBA Array Variables
Boolean Data Type (Dim Variable)
Constant
VBA Constant Expression Required
VBA Data Types – Variables and Constants
VBA Date Variable
Double Data Type (Dim Variable)
VBA Global Variable
Int / Integer Data Type (Dim Variable)
Long Data Type (Dim Variable)
VBA Public Variable – Use a Variable in Multiple Modules or Subs
VBA Range Object Variables (Dim / Set Range)
Set Object Variables – Workbooks, Worksheets, & More
Single Data Type (Dim Variable)
VBA Static Variable
String Data Type (Dim Variable)
Variant Data Type (Dim Variable)
Settingsyes
Get and Change User Name with VBA
VBA – Display Status Bar Message
VBA – Modify the titlebar
VBA – Prevent warning messages from a macro
VBA – Turn Automatic Calculations Off (or On)
VBA – Turn Formula Bar Off (or On)
VBA – Turn off Screen Updating
VBA Turn Scroll Bars On or Off
Text and Stringsyes
Prevent VBA Case Sensitive – Option Compare Text
VBA – Extract Number From String
VBA – Find the nth Word in a String of Text
VBA – Line Break or Line Feed in a Message Box
VBA – Loop Through a String
VBA – Remove Characters from Left or Right Side of Variable Length String
VBA Char / Chr Function
VBA Compare Strings – StrComp
VBA Comparison Operators – Not Equal to & More
VBA Concatenate Text Strings Together (& – Ampersand)
Convert Integer (Number) to String
Convert String to Date
Convert Text String to Number
VBA INSTR – Find Text in a String
VBA Like Operator
VBA Logical Operators – OR, AND, XOR, NOT, IS, & LIKE
VBA New Line / Carriage Return
Parse String
VBA Space Function – Add Spaces to a String
VBA Strings and Substrings Functions
Text to Columns
Trim, LTrim, and RTrim Functions – Remove Spaces From Text
VBA Upper, Lower, and Proper Case – Case Functions
Viewyes
VBA – ActiveWindow.WindowState
VBA – Display (or Exit) Fullscreen
VBA – Freeze Panes
VBA – Hide Excel (The Entire Application)
VBA – Scroll Vertically and Scroll Horizontally
VBA – Zoom in and Out of Worksheets
Filesyes
FileSystem Object
Move Files with VBA FileSystemObject (MoveFile)
Create Text File with CreateTextFile
VBA – Delete files from the hard drive
Read Text File into String
Read Text File Line by Line
VBA – Test if Workbook is Open by Workbook Name
Validate Filenames
Write to Text File
VBA Check if File or Folder Exists
Combine Multiple Excel Files into One Workbook
Copy a Folder with CopyFolder (FSO)
VBA Copy File / Workbook
VBA Delete File / Workbook
Delete Folder with DeleteFolder (FSO)
FileCopy Statement
Get File Name with GetFileName (FSO)
GetFolder & GetFile (Get File & Folder Properties)
VBA List Files in Folder
VBA Open File Dialog Box
Open Text File with OpenTextFile
Read Text File (Read, Parse, and Import)
VBA Rename File
Shapes, Charts, Objectsyes
VBA AutoFilter
VBA Guide For Charts and Graphs
VBA Guide to Pivot Tables
VBA Objects
Excel Add VBA Button
How to use VBA Controls in UserForms
VBA – Delete All Autoshapes
VBA – Programmatically Draw Boxes with Code
Listbox – Selected Item
VBA Tables and ListObjects
TextBox
Formattingyes
Conditional Formatting
Bold
Cell Borders
Cell Font – Change Color, Size, Style, & More
Center Text – Cell Alignment (Horizontal & Vertical)
Display Page Breaks Setting
Format Cells
Format Date
Format Time
Eventsyes
Events
VBA – Exit Without Save Prompt
VBA – Force a Workbook to Save Before Close
VBA – Run a Macro when Excel Closes – Auto_Close
VBA – Run a macro when Excel starts
VBA Worksheet Change Event – Run a Macro When a Cell Changes
VBA: Show or load a form when Excel starts
UserForms, MsgBoxes & Controlsyes
VBA – Yes No Message Box (Msgbox)
VBA InputBox – Get Input from a User
Creating VBA Userforms
Option Button Excel VBA
Spin button Excel VBA
VBA Checkbox
VBA ComboBox
VBA Listbox
VBA Open or Close UserForm
Formattingyes
Conditional Formatting
Bold
Cell Borders
Cell Font – Change Color, Size, Style, & More
Center Text – Cell Alignment (Horizontal & Vertical)
Display Page Breaks Setting
Format Cells
Format Date
Format Time
Misc.yes
Clear Clipboard
Pause (Break / Stop) & Resume a Macro
Save As (Print) to PDF
ColorIndex Codes List
Format Numbers
How to Install (or Uninstall) a VBA add-in (.xlam file) for Microsoft Excel
How to use VBA Structures
Removing Duplicate Values in Excel VBA
VBA – Add or Remove Cell Comments
All Operators Explained
Check if Office App is Running
Dialog Box
Get Today’s Date (Current Date)
Pivot Table Filter
Refresh Pivot Table / All Pivot Tables
Refresh Pivot Table when Data Changes
Using Goal Seek in VBA
Using the Analysis ToolPak in VBA
Advanced Filter
Application.CutCopyMode = False
Calculate – Now, Workbook, Worksheet, or Range
Call / Run Sub From another Subroutine
Copy to Clipboard
Find Value in Column
GoTo a Line Label
IIF Function
Input Statement
is Nothing
Is Operator
Line Input Statement
Multiple (Nested) If Statements
Project Password / Protect code
Solver
Wait & Sleep Functions – Pause / Delay Code
Wildcards
With / End With Statement
What is the Difference Between VB and VBA?
Personal Macro Workbook
VBA – Turn Off AutoFilter / Clear Filters
VBA Drop Down List (Data Validation)
VBA Hyperlinks
VBA Random Number
Round, RoundUp, and RoundDown
Split Function – Split String of Text into Array
Sqr Function
Sum Function (Ranges, Columns, & More)
SUMIF and SUMIFS Functions
Switch Statement
Text Function
Time Functions
Timer Function
TypeName
TypeOf
UBound & LBound Functions
Val Function
VarType
Write Statement
Vlookup – Multiple Results with VBA
Advanced Programmingyes
Advanced VBA Programming Examples
Early Late Binding
Class Constructor
Class Module Tutorial & Examples
Matrix – Create & More
Shell
VBA – Convert Matrix to Vector
VBA Regex
VBA SendKeys
VBA Timer Function
Internetyes
Automate Internet Explorer (IE) Using VBA
VBA – Consume Soap Web Services
VBA – WinHttpRequest with Login and Password – Misc Bloglines
Send Emails from Excel through Gmail
Send Emails from Excel through Outlook
Accessyes
Access VBA Recordsets – Open, Count, Loop and More
Access VBA – Import / Export Excel – Query, Report, Table, and Forms
Access VBA – Open / Close Form
Access VBA – Open Excel File
Access VBA Database – Open, Connect, Login, & More
Access VBA Findfirst
Access VBA Me
Access VBA Query
Access VBA Recordsets – Open, Count, Loop and More
Access VBA Refresh Form
Access VBA Reports – Print, Export, Filter
Access VBA SQL Examples
Access VBA Tables – Update, Count, Delete, Create, Rename, Export
Wordyes
Word VBA – Macro to Open Word Document
Word VBA Macros – Find, Find & Replace
Word VBA Macros – SaveAs (PDF or New File Name)

AI Formula Generator

Try for Free


Excel Macro Examples

Below you will find a list of basic macro examples for common Excel automation tasks.

Copy and Paste a Row from One Sheet to Another

This super simple macro will copy a row from one sheet to another.

Sub Paste_OneRow()
'Copy and Paste Row
Sheets("sheet1").Range("1:1").Copy Sheets("sheet2").Range("1:1")

Application.CutCopyMode = False

End Sub

Send Email

This useful macro will launch Outlook, draft an email, and attach the ActiveWorkbook.

Sub Send_Mail()
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .to = "test@test.com"
        .Subject = "Test Email"
        .Body = "Message Body"
        .Attachments.Add ActiveWorkbook.FullName
        .Display
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

List All Sheets in Workbook

This macro will list all sheets in a workbook.

Sub ListSheets()
    
    Dim ws As Worksheet
    Dim x As Integer
    
    x = 1
    
    ActiveSheet.Range("A:A").Clear
    
    For Each ws In Worksheets
        ActiveSheet.Cells(x, 1) = ws.Name
        x = x + 1
    Next ws
    
End Sub

Unhide All Worksheets

This macro will unhide all worksheets.

' Unhide All Worksheets
Sub UnhideAllWoksheets()
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
    
End Sub

Hide All Worksheets Except Active

This macro will hide all worksheets except the active worksheet.

' Hide All Sheets Except Active Sheet
Sub HideAllExceptActiveSheet()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden
    Next ws
    
End Sub

Unprotect All Worksheets

This macro example will unprotect all worksheets in a workbook.


' UnProtect All Worksheets
Sub UnProtectAllSheets()
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        ws.Unprotect "password"
    Next ws
    
End Sub

Protect All Worksheets

This macro will protect all worksheets in a workbook.

' Protect All Worksheets
Sub ProtectAllSheets()
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        ws.protect "password"
    Next ws
    
End Sub

Delete All Shapes

This macro will delete all shapes in a worksheet.

Sub DeleteAllShapes()

Dim GetShape As Shape

For Each GetShape In ActiveSheet.Shapes
  GetShape.Delete
Next

End Sub

Delete All Blank Rows in Worksheet

This example macro will delete all blank rows in a worksheet.

Sub DeleteBlankRows()
Dim x As Long

With ActiveSheet
    For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
        If WorksheetFunction.CountA(.Rows(x)) = 0 Then
            ActiveSheet.Rows(x).Delete
        End If
    Next
End With

End Sub

Highlight Duplicate Values in Selection

Use this simple macro to highlight all duplicate values in a selection.


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

Highlight Negative Numbers

This macro automates the task of highlighting negative numbers.

' Highlight Negative Numbers
Sub HighlightNegativeNumbers()
    Dim myRange As Range
    Dim cell As Range
    
    Set myRange = Selection
    
    For Each cell In myRange
        If cell.Value < 0 Then
            cell.Interior.ColorIndex = 36
        End If
    Next cell
End Sub

Highlight Alternate Rows

This macro is useful to highlight alternate rows.

' Highlight Alternate Rows
Sub highlightAlternateRows()
    Dim cell As Range
    Dim myRange As Range
    
    myRange = Selection
    
    For Each cell In myRange.Rows
        If cell.Row Mod 2 = 1 Then
            cell.Interior.ColorIndex = 36
        End If
    Next cell
End Sub

Highlight Blank Cells in Selection

This basic macro highlights blank cells in a selection.

' Highlight all Blank Cells in Selection
Sub HighlightBlankCells()
    Dim rng As Range
    
    Set rng = Selection
    rng.SpecialCells(xlCellTypeBlanks).Interior.Color = vbCyan
    
End Sub

Excel VBA Macros Examples – Free Download

We’ve created a free VBA (Macros) Code Examples add-in. The add-in contains over 100 ready-to-use macro examples, including the macro examples above!

excel vba examples free download
Download Page

Excel Macro / VBA FAQs

How to write VBA code (Macros) in Excel?

To write VBA code in Excel open up the VBA Editor (ALT + F11). Type “Sub HelloWorld”, Press Enter, and you’ve created a Macro! OR Copy and paste one of the procedures listed on this page into the code window.

What is Excel VBA?

VBA is the programming language used to automate Excel.

How to use VBA to automate Excel?

You use VBA to automate Excel by creating Macros. Macros are blocks of code that complete certain tasks.


Practice VBA

You can practice VBA with our interactive VBA tutorial.

practice vba macros