Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

Translate Common VBA Macros to Python

A phrasebook for moving VBA to Python: the eight patterns that make up most Excel macros — finding the last row, looping over rows, deleting rows that match, lookups with a dictionary, filtering and copying, looping over a folder of files, saving a copy with a date, and error handling — each shown in VBA and in pandas or openpyxl.
Macros & VBAopenpyxl

Most Excel macros are built from the same handful of moves: find where the data ends, loop down the rows, delete some of them, look values up, copy the survivors to another sheet, repeat for every file in a folder, save a dated copy. Once you know what each move becomes in Python, translating a macro is mostly a matter of recognising the patterns and replacing loops with operations on whole columns. This page is a phrasebook: each section shows a typical VBA fragment and its Python equivalent, run against the same small workbook. It belongs to Choosing a Python Excel Library; for the case for and against moving at all, see Python vs VBA for Excel Automation.

Prerequisites

Bash
pip install pandas openpyxl

orders.xlsx has an Orders sheet (order ID, region, SKU, quantity, status) and a Prices sheet mapping SKU to unit price; the monthly/ folder holds three monthly exports and, as real folders often do, an Excel lock file. The VBA fragments are the ones found in real macros, trimmed to the essential lines.

The main shift when translating Two panels. VBA works cell by cell in a live Excel session, looping over rows and changing the sheet as it goes. Python reads the sheet into a table, applies operations to whole columns, and writes the result once. VBA cell by cell loops over rows changes the sheet in place needs Excel open Python whole columns at once filters instead of loops writes the result once runs anywhere

1. Find the last row

VBA's most common line finds where the data ends, so loops know when to stop:

Vb
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

In pandas the question disappears: read_excel reads exactly the rows that hold data. When you work with openpyxl cells directly, max_row is the equivalent, with the caveat that formatted but empty rows count too:

Python
from openpyxl import load_workbook

df = pd.read_excel("orders.xlsx", sheet_name="Orders")
print("pandas rows:", len(df))

ws = load_workbook("orders.xlsx")["Orders"]
print("openpyxl max_row:", ws.max_row, "(including the header)")

To match End(xlUp) exactly — the last non-empty cell in one column — walk up from max_row until a value appears. Find the Last Row with Data in openpyxl covers that and the other edge cases.

2. Loop over rows and compute a column

Vb
For r = 2 To lastRow
    ws.Cells(r, 6).Value = ws.Cells(r, 4).Value * 1.2
Next r

The loop becomes one assignment to a new column. Every row is computed at once:

Python
df["QtyWithBuffer"] = df["Qty"] * 1.2
print(df[["OrderID", "Qty", "QtyWithBuffer"]].head(3))

When the row logic has branches — If ... ElseIf ... Else — use numpy.select or Series.where rather than going back to a loop:

Python
import numpy as np

df["Priority"] = np.select(
    [df["Qty"] >= 10, df["Qty"] >= 4],
    ["High", "Normal"],
    default="Low",
)
print(df[["OrderID", "Qty", "Priority"]].to_string(index=False))

3. Delete rows that match a condition

VBA has to loop backwards, because deleting a row shifts everything below it up:

Vb
For r = lastRow To 2 Step -1
    If ws.Cells(r, 5).Value = "Cancelled" Then ws.Rows(r).Delete
Next r

In pandas you keep the rows you want instead of deleting the ones you don't. There is no direction to get wrong:

Python
kept = df[df["Status"] != "Cancelled"]
print(f"{len(df)} rows -> {len(kept)} rows")

If the sheet must be edited in place with openpyxl, to keep its formatting for example, the backwards loop comes back, for the same reason as in VBA: ws.delete_rows() shifts the rows below. Insert and Delete Rows and Columns with openpyxl shows the reverse-order rule.

Deleting rows becomes keeping rows Part of the orders table on the left has two cancelled rows. A filter that keeps rows whose status is not Cancelled produces the table on the right keeping the remaining rows in their original order. 1 OrderID Status 2 5001 Shipped 3 5002 Cancelled 4 5003 Shipped 5 5006 Cancelled 6 5007 Open 1 OrderID Status 2 5001 Shipped 3 5003 Shipped 4 5007 Open before after Status != Cancelled

4. Look values up with a dictionary

Macros that look up prices, names or codes usually load a Scripting.Dictionary first, then read from it in the row loop:

Vb
Set prices = CreateObject("Scripting.Dictionary")
For r = 2 To pLast
    prices(wsP.Cells(r, 1).Value) = wsP.Cells(r, 2).Value
Next r
For r = 2 To lastRow
    If prices.Exists(ws.Cells(r, 3).Value) Then
        ws.Cells(r, 7).Value = prices(ws.Cells(r, 3).Value)
    Else
        ws.Cells(r, 7).Value = "missing"
    End If
Next r

A Python dictionary does the same thing, and Series.map applies it to a whole column. A left merge is the table-shaped equivalent; both leave missing keys empty rather than raising an error:

Python
prices = pd.read_excel("orders.xlsx", sheet_name="Prices")
lookup = dict(zip(prices["SKU"], prices["Price"]))
kept = kept.assign(Price=kept["SKU"].map(lookup))
kept["Value"] = kept["Qty"] * kept["Price"]
print(kept[["OrderID", "SKU", "Qty", "Price", "Value"]].to_string(index=False))
print("SKUs without a price:", sorted(kept.loc[kept["Price"].isna(), "SKU"].unique()))

D-40 has no price, the case the VBA marks "missing". List those keys explicitly rather than letting them turn into silent blanks. The VLOOKUP Equivalent in pandas for Excel Files goes through the merge options.

5. Filter and copy to another sheet

Vb
ws.Range("A1").AutoFilter Field:=2, Criteria1:="North"
ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy wsNorth.Range("A1")
ws.AutoFilterMode = False

Filter the table and write the result as its own sheet. Doing every region in one loop is how the "one sheet per region" macro usually ends:

Python
with pd.ExcelWriter("by_region.xlsx") as writer:
    for region, part in kept.groupby("Region"):
        part.to_excel(writer, sheet_name=region, index=False)
print(load_workbook("by_region.xlsx").sheetnames)

6. Loop over every file in a folder

Vb
f = Dir(folder & "\*.xlsx")
Do While f <> ""
    Set wb = Workbooks.Open(folder & "\" & f)
    ' ... copy the data out ...
    wb.Close SaveChanges:=False
    f = Dir()
Loop

pathlib replaces Dir(). Skip Excel's ~$ lock files, which a VBA macro running inside Excel rarely meets but a script running beside users often does:

Python
from pathlib import Path

frames = []
for path in sorted(Path("monthly").glob("*.xlsx")):
    if path.name.startswith("~$"):
        print("skipping lock file", path.name)
        continue
    frames.append(pd.read_excel(path))
combined = pd.concat(frames, ignore_index=True)
print(combined.groupby("Month").size().to_string())

7. Save a copy with today's date

Vb
wb.SaveCopyAs folder & "\Orders_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
Python
from datetime import date

out = Path("exports") / f"Orders_{date(2026, 9, 29):%Y-%m-%d}.xlsx"
out.parent.mkdir(exist_ok=True)
kept.to_excel(out, index=False)
print(out)

In a real job use date.today(); the fixed date keeps this example's output stable.

8. Error handling

On Error Resume Next hides every error for the rest of the procedure, which is how macros quietly produce half a report. Python's equivalent is narrower: catch the specific error, where it can happen, and say what happened:

Vb
On Error Resume Next
Set wb = Workbooks.Open(path)
If wb Is Nothing Then MsgBox "Could not open " & path
On Error GoTo 0
Python
for path in [Path("monthly/orders_2026-07.xlsx"), Path("monthly/orders_2026-10.xlsx")]:
    try:
        rows = len(pd.read_excel(path))
    except FileNotFoundError:
        print(f"missing: {path.name} - skipped")
        continue
    print(f"read {path.name}: {rows} rows")

Replace MsgBox with logging in scheduled jobs, since nobody is there to click OK. Log Python Excel Script Output to a File sets that up.

VBA idioms and their Python counterparts A reference table. End(xlUp) becomes reading the table or max_row. A For loop over rows becomes a column operation. Deleting rows becomes a boolean filter. Scripting.Dictionary becomes a dict with Series.map. AutoFilter and copy becomes groupby and to_excel. Dir becomes pathlib glob. SaveCopyAs becomes to_excel with a dated name. On Error becomes try and except. VBA Python End(xlUp).Row read_excel / max_row For r = 2 To last column expression Rows(r).Delete df[condition] Scripting.Dictionary dict + Series.map AutoFilter + Copy groupby + to_excel Dir() Path.glob SaveCopyAs to_excel dated name On Error try / except

What does not translate

A few things macros do have no file-based equivalent, because they depend on Excel running:

  • Recalculation. openpyxl writes formulas but does not calculate them. If the macro relies on Excel computing values before reading them back, compute in Python instead, or recalculate with LibreOffice or Excel as a step.
  • User interaction. Input boxes, forms and buttons belong in Excel. Keep those parts in VBA, or replace them with parameters to the script.
  • Pivot refreshes and charts copied from templates. Refreshing a live PivotTable needs Excel; building the summary with pivot_table and writing a chart with openpyxl is the Python route.

For those, driving Excel from Python is the bridge: Run an Excel Macro from Python with win32com lets a script call the remaining VBA while the rest moves over.

Checking the translation

Run the old macro and the new script on the same input and compare the outputs before switching. The comparison should be on values, not formatting, and should report differing cells rather than just "different": Compare Two Excel Files for Differences with Python builds that check. To see what the macros contain before starting, Extract VBA Macro Code from an Excel File lists every module in a folder of workbooks.

Common pitfalls

  • Translating line by line. A literal port keeps the row loops and runs slower than the original; translate the intent of each block.
  • Row numbers. VBA rows start at 1 and include the header; pandas indices start at 0 and do not. Avoid using positions at all where a column value will do.
  • Blank versus zero. VBA's Cells(r, c).Value is Empty for a blank cell and compares equal to 0 and ""; in pandas a blank is NaN and equals nothing, including itself.
  • Dates. Macros often rely on Excel's locale to parse dates typed as text; parse them explicitly with a format in Python.
  • Silent errors. Don't reproduce On Error Resume Next with a bare except:; catch the specific error.

Conclusion

Translate macros pattern by pattern: reading the table replaces End(xlUp), column expressions replace row loops, filters replace backward delete loops, dict and Series.map replace Scripting.Dictionary, groupby replaces AutoFilter and copy, pathlib replaces Dir(), and specific try/except blocks replace On Error Resume Next. Keep what depends on a running Excel — recalculation, forms, live pivots — in VBA or drive it with win32com, and compare old and new outputs before retiring the macro.

Frequently asked questions

Is there a tool that converts VBA to Python automatically? Not one that produces good Python. Line-by-line converters keep VBA's cell loops and Excel object model. Translating by pattern into pandas and openpyxl gives shorter, faster code that runs without Excel.

How do I find the last row in Python like End(xlUp)? With pandas, read_excel only returns rows with data, so len(df) is the count. With openpyxl, ws.max_row gives the last used row, which can include formatted but empty rows; walk up from it to find the last value in a column.

What replaces Scripting.Dictionary in Python? A built-in dict. Build it from two columns with dict(zip(keys, values)) and apply it to a column with Series.map; keys that are not found become NaN.

Can Python run the parts of a macro I have not translated yet? Yes, on Windows with Excel installed. win32com or xlwings can open the workbook and call a macro by name, so the script and the remaining VBA can run in sequence.

More on macros & vba

More on macros & vba →