Create a simple VBA macro in Excel to automate a task

You have a repetitive task in Excel — formatting a table, deleting empty rows, applying formats, or consolidating data — and you are tired of repeating the same actions. A VBA macro allows you to automate these actions in seconds. This article guides you step-by-step to create a simple macro, understand the generated code, adapt it to your needs, and avoid common pitfalls, without assuming you are a developer.

In brief

Why automate with a VBA macro?

Automating is not just about saving time; it also reduces human errors and standardizes the output. When a task always follows the same rules (select a range, sort, apply formats, copy-paste results), repetition leads to omissions: an unformatted column, a row forgotten during sorting. A macro executes the sequence identically every time. One might think recording is enough, but often you need to understand and adjust the code to make it robust against data variations.

Before starting: precautions and preparation

Saving and file format

Save your workbook in .xlsm format (macro-enabled workbook) before recording or pasting VBA code. This prevents losing macros and allows easy recovery in case of problems. Work on a copy if you automate destructive operations (deleting rows, overwriting cells).

Enable the Developer tab and security

The Developer tab is not visible by default in Excel: enable it via Options > Customize Ribbon. On the security side, adjust the macro management center: prefer “Disable all macros with notification” so you can enable trusted macros as needed, and avoid automatically allowing all macros from unknown sources.

Check the source data

Before automating, examine your data: column types (text, number, date), decimal separators, merged cells. If your data comes from a CSV file, import it properly and correct common errors — a wrong separator can turn a number into text and cause the macro to fail.

Choice of method: record or code?

Two approaches coexist: use the macro recorder or write VBA code directly. The recorder is quick for capturing simple actions and learning the syntax, but it often produces verbose code dependent on precise addresses. Writing the code by hand requires some thought but results in a cleaner, more adaptable, and maintainable macro.

Method	Advantages	Disadvantages
Record the macro	Fast, accessible to beginners	Verbose code, fragile to structural changes
Write the code	Flexible, optimizable, more robust	Steeper learning curve

Practical example: macro to clean and format a table

We will create a simple macro that:

• selects the used range;
• deletes empty rows;
• formats the header (bold, background);
• applies a numeric format to columns detected as numbers;
• auto-adjusts the column widths.

This sequence of actions is typical of preprocessing before report generation or export. Here is the code, which you can paste into the VBA editor (ALT+F11) in a standard module:

Sub NettoyerEtFormaterTableau()
    Dim ws As Worksheet
    Dim plage As Range
    Dim derniereLigne As Long, derniereCol As Long
    Set ws = ActiveSheet

    ' Determine the used range
    With ws
        If Application.WorksheetFunction.CountA(.Cells) = 0 Then Exit Sub
        derniereLigne = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        derniereCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set plage = .Range(.Cells(1, 1), .Cells(derniereLigne, derniereCol))
    End With

    ' Delete entirely empty rows
    Dim i As Long
    For i = derniereLigne To 1 Step -1
        If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then ws.Rows(i).Delete
    Next i

    ' Format the header (first row)
    With plage.Rows(1).Font
        .Bold = True
    End With
    plage.Rows(1).Interior.Color = RGB(220, 230, 241)

    ' Apply a numeric format to columns that appear numeric
    Dim col As Long, sampleVal As Variant
    For col = 1 To derniereCol
        sampleVal = Application.WorksheetFunction.Index(ws.Columns(col).Value, 2, 1)
        If IsNumeric(sampleVal) Then
            ws.Columns(col).NumberFormat = "#,##0.00"
        End If
    Next col

    ' Adjust the columns
    plage.Columns.AutoFit
End Sub

Detailed explanation of the code

The initial lines define the sheet and determine the used range via the Find method, which is more reliable than UsedRange to ignore formatted cells without content. Deleting empty rows is done in a reverse loop to avoid index shifts. The header format uses Interior.Color for a light background; you can replace RGB with an Excel constant if you prefer. Identifying numeric columns relies on a sample (here the cell in the second row), a simple but effective technique to avoid applying a numeric format to an ID column containing leading zeros.

Adapting the macro to other contexts

Robustness comes from small protections: checking that the sheet is not empty, handling possible errors, documenting the code. To evolve the macro for multi-sheet use, add a step asking the user to choose the sheet or loop through all sheets in the workbook. If your data is imported automatically, insert the import procedure before cleaning.

Dialog box and execution via button

To make the macro accessible to non-technical users, you can associate it with a button on the sheet (Insert > Shapes, then assign the macro) or create a small user form (UserForm) to enter parameters (range, include/uncheck deletion of empty rows, etc.).

Best practices and pitfalls to avoid

• Comment your code: two or three words are enough to explain each logical block. You will thank yourself later.
• Test on a sample dataset before running on critical data.
• Handle errors: a simple On Error GoTo ErrorHandler and a clear message prevent incomprehensible crashes for the user.
• Avoid absolute references: use dynamic methods (Find, CurrentRegion, Named Tables) rather than Range(“A1:Z100”).

Summary table: common errors and solutions

Symptom	Common cause	Solution
Macro stops with error 13 (Type mismatch)	Unexpected value (text vs number)	Check types with IsNumeric / IsDate; add conversions
Different result depending on the sheet	Reference to ActiveSheet not anticipated	Specify ws = ThisWorkbook.Worksheets(“Name”) or ask for the sheet
Columns not adjusted	Conditional formatting or merged cells	Remove merges, handle conditional formats before AutoFit

FAQ

How to run a macro automatically when opening the file?

Place a sub named Workbook_Open() in the ThisWorkbook module. This procedure will run automatically when the workbook opens. Warning: avoid running risky actions without user confirmation.

What is the difference between Sub and Function in VBA?

A Sub executes a series of instructions without returning a value; a Function returns a value and can be used in a cell as a custom formula or called from a Sub.

The macro deletes data, can I undo?

Excel does not allow undoing a macro (Ctrl+Z does not work after running a script). To prevent losses, make an automatic backup copy at the start of the macro or use the Versioning feature of your environment (OneDrive, SharePoint).

Can a macro be run on multiple files?

Yes, by opening each file via VBA and applying the same procedure. Create a “master” macro that loops through a folder, opens each file, runs the cleaning macro, and saves the file.

{
“@context”: “https://schema.org”,
“@type”: “ImageObject”,
“identifier”: “AI_IMAGE_METADATA”,
“slug”: “vba-macro-excel-example”,
“prompt”: “Realistic and aesthetic capture of a computer screen showing Excel and the VBA editor open side by side: on the left, an Excel sheet with a data table and formatted headers (light background, bold texts), on the right the VBA editor displaying a module with readable code to clean and format a table. Soft light, professional style, high resolution, blue and white color palette.”,
“alt”: “Example of VBA editor and Excel sheet showing a macro to clean and format a table”
}

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How to run a macro automatically when the file opens?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Place a sub named Workbook_Open() in the ThisWorkbook module. This procedure will run automatically when the workbook opens. Warning: avoid executing risky actions without user confirmation.”
}
},
{
“@type”: “Question”,
“name”: “What is the difference between Sub and Function in VBA?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A Sub executes a series of instructions without returning a value; a Function returns a value and can be used in a cell as a custom formula or called from a Sub.”
}
},
{
“@type”: “Question”,
“name”: “The macro deletes data, can I undo?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Excel does not allow undoing a macro (Ctrl+Z does not work after running a script). To prevent losses, make an automatic backup copy at the start of the macro or use the Versioning feature of your environment (OneDrive, SharePoint).”
}
}
]
}

{
“@context”: “https://schema.org”,
“@type”: “WebPage”,
“about”: {
“@type”: “Thing”,
“name”: “Simple VBA Macro to Automate a Task in Excel”
},
“keywords”: [“VBA macro”, “Excel”, “automation”, “record macro”, “VBA editor”]
}