DEV Community

Tùng Nguyễn
Tùng Nguyễn

Posted on

Automating Excel Reporting & Data Cleaning: 10 Essential VBA Macros + Python Pipeline

#python #automation #tutorial #productivity

Even in an era of cloud data warehouses and Python notebooks, Microsoft Excel remains the final delivery format for 90% of finance, operations, e-commerce, and sales teams.

When your team spends hours every week manually deduplicating rows, trimming invisible non-breaking spaces (CHAR(160)), merging multi-tab CSV exports, and exporting filtered PDF reports, combining Python (openpyxl / pandas) with 1-Click Excel VBA Macros (.bas) turns a 3-hour Friday chore into a 5-second workflow.

⚡ Ready-to-Use Excel VBA & Python Automation Packs

🛠️ 3 Production-Grade VBA Macros Every Analyst Should Have

1. Deep-Clean Whitespace, Non-Breaking Spaces (CHAR(160)) & Case Normalization

Standard WorksheetFunction.Trim in Excel does not remove HTML non-breaking spaces (Chr(160)) copied from web pages or CRM exports, which silently breaks VLOOKUP and XLOOKUP.

Sub CleanSelectedRangeForXLOOKUP()
    Dim rng As Range, cell As Range
    On Error Resume Next
    Set rng = Intersect(Selection, ActiveSheet.UsedRange)
    On Error GoTo 0
    If rng Is Nothing Then Exit Sub

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each cell In rng.Cells
        If Not cell.HasFormula And Not IsEmpty(cell.Value) Then
            Dim cleanVal As String
            cleanVal = CStr(cell.Value)
            cleanVal = Replace(cleanVal, Chr(160), " ")
            cleanVal = Replace(cleanVal, vbCr, " ")
            cleanVal = Replace(cleanVal, vbLf, " ")
            cleanVal = Application.WorksheetFunction.Trim(cleanVal)
            cell.Value = cleanVal
        End If
    Next cell

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    MsgBox "Cleaned " & rng.Cells.Count & " cells!", vbInformation, "Clean Complete"
End Sub
Enter fullscreen mode Exit fullscreen mode

2. Merge All CSV / XLSX Files in a Folder into One Master Sheet

Instead of opening 30 daily sales exports one by one, this macro loops through a chosen folder, copies the data rows below the header from each file, and tags every row with the source filename:

Sub MergeFolderCSVsIntoMaster()
    Dim folderPath As String, fileName As String
    Dim wbSource As Workbook, wsMaster As Worksheet
    Dim lastRow As Long, srcRows As Long

    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Folder Containing CSV/XLSX Reports"
        If .Show <> -1 Then Exit Sub
        folderPath = .SelectedItems(1) & Application.PathSeparator
    End With

    Application.ScreenUpdating = False
    Set wsMaster = ThisWorkbook.Sheets.Add
    wsMaster.Name = "Merged_" & Format(Now, "hhmmss")

    fileName = Dir(folderPath & "*.csv")
    Dim isFirst As Boolean: isFirst = True

    Do While fileName <> ""
        Set wbSource = Workbooks.Open(folderPath & fileName, ReadOnly:=True)
        With wbSource.Sheets(1).UsedRange
            If isFirst Then
                .Copy wsMaster.Range("A1")
                isFirst = False
            ElseIf .Rows.Count > 1 Then
                lastRow = wsMaster.Cells(wsMaster.Rows.Count, 1).End(xlUp).Row + 1
                .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Copy wsMaster.Cells(lastRow, 1)
            End If
        End With
        wbSource.Close SaveChanges:=False
        fileName = Dir()
    Loop

    wsMaster.Columns.AutoFit
    Application.ScreenUpdating = True
    MsgBox "All files merged into " & wsMaster.Name, vbInformation
End Sub
Enter fullscreen mode Exit fullscreen mode

3. Auto-Generate Formatted Excel Workbooks from Python (openpyxl)

When pairing scraped web data (from our RapidAPI Hub REST APIs) with Excel delivery, openpyxl lets you apply frozen headers, auto-filters, and conditional fills automatically:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

def export_leads_to_excel(rows: list[dict], out_path: str = "b2b_leads.xlsx") -> None:
    wb = Workbook()
    ws = wb.active
    ws.title = "Verified Leads"

    headers = ["Company URL", "Title", "Primary Email", "Phone", "LinkedIn"]
    ws.append(headers)

    header_fill = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid")
    header_font = Font(color="FFFFFF", bold=True)
    for col_idx in range(1, len(headers) + 1):
        cell = ws.cell(row=1, column=col_idx)
        cell.fill = header_fill
        cell.font = header_font

    for r in rows:
        ws.append([
            r.get("url", ""),
            r.get("title", ""),
            (r.get("emails") or [""])[0],
            (r.get("phones") or [""])[0],
            ((r.get("socials") or {}).get("linkedin") or [""])[0],
        ])

    ws.freeze_panes = "A2"
    ws.auto_filter.ref = ws.dimensions
    wb.save(out_path)
Enter fullscreen mode Exit fullscreen mode

📦 Grab the Complete 10-Macro Pack + Python Suites

Top comments (0)