Write an Excel VBA Macro That Cleans and Formats Your Recurring Report

You get a commented VBA macro built from your own column headers, with backup steps, install instructions and a post-run checklist.

Prompt

Prompt
You are an Excel automation specialist who writes VBA for people who have never opened the VBA editor.

Your job: write a macro that cleans and formats a report I receive every week or month. I will paste the column headers and 5-10 sample rows right after this prompt. Before writing any code, ask me up to 4 short numbered questions in one message (for example: what to do with blank rows, which columns are dates or money, how to sort, whether the sheet name changes) and wait for my answers.

Rules:
1. Put a plain-English comment above every block saying what it does and why.
2. Find columns by header name, not by letter, so the macro still works if columns move.
3. Never change the original data: copy it to a new sheet named Cleaned and work there.
4. Use only built-in VBA, no add-ins or external references.
5. End with a message box showing how many rows were kept and removed.

Output format:
1. Before you start: tell me to save a backup copy and save the file as .xlsm (Excel Macro-Enabled Workbook).
2. The full macro in one code block.
3. How to install and run it: numbered steps from Alt+F11 to clicking Run.
4. What to check after the first run: 3-5 bullets.

End with one line listing the assumptions you made about my data and what could break the macro.

Example output

Example for: a weekly sales export with Order ID, Order Date, Customer, Region, Amount and Status; remove blank and Cancelled rows, sort newest first.

1. Before you start
  • Save a backup copy of the report (File > Save a Copy).
  • Save the working file as Excel Macro-Enabled Workbook (.xlsm), or the macro will be lost when you close it.
2. The macro
Sub CleanWeeklySales()
    ' Pause screen redraws so the macro runs faster
    Application.ScreenUpdating = False
    Dim src As Worksheet, ws As Worksheet
    Dim lastRow As Long, r As Long, removed As Long
    Dim colDate As Long, colAmt As Long, colStatus As Long

    ' Copy the raw sheet so the original data is never touched
    Set src = ActiveSheet
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("Cleaned").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    src.Copy After:=src
    Set ws = ActiveSheet
    ws.Name = "Cleaned"

    ' Find columns by header text so moved columns still work
    colDate = FindCol(ws, "Order Date")
    colAmt = FindCol(ws, "Amount")
    colStatus = FindCol(ws, "Status")
    If colDate = 0 Or colAmt = 0 Or colStatus = 0 Then
        MsgBox "A required header is missing in row 1.": Exit Sub
    End If

    ' Go bottom-up and delete blank or Cancelled rows
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For r = lastRow To 2 Step -1
        If Application.WorksheetFunction.CountA(ws.Rows(r)) = 0 _
           Or Trim(ws.Cells(r, colStatus).Value) = "Cancelled" Then
            ws.Rows(r).Delete
            removed = removed + 1
        End If
    Next r

    ' Format dates, money and the header row
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ws.Range(ws.Cells(2, colDate), ws.Cells(lastRow, colDate)).NumberFormat = "dd-mmm-yyyy"
    ws.Range(ws.Cells(2, colAmt), ws.Cells(lastRow, colAmt)).NumberFormat = "$#,##0.00"
    ws.Rows(1).Font.Bold = True
    ws.Columns.AutoFit

    ' Sort newest orders first
    ws.Range("A1").CurrentRegion.Sort Key1:=ws.Cells(1, colDate), _
        Order1:=xlDescending, Header:=xlYes

    Application.ScreenUpdating = True
    MsgBox "Done. Rows kept: " & lastRow - 1 & ". Rows removed: " & removed
End Sub

' Helper: returns the column number of a header, or 0 if not found
Function FindCol(ws As Worksheet, header As String) As Long
    Dim c As Range
    Set c = ws.Rows(1).Find(What:=header, LookAt:=xlWhole)
    If Not c Is Nothing Then FindCol = c.Column
End Function
3. How to install and run it
  1. Open the .xlsm file and press Alt+F11.
  2. Click Insert > Module.
  3. Paste the whole macro into the blank window.
  4. Close the editor, click the raw report sheet, press Alt+F8, pick CleanWeeklySales, click Run.
4. What to check after the first run
  • Row count in the message box matches what you expect.
  • No Cancelled orders remain on the Cleaned sheet.
  • Dates sort correctly (text dates will not).
  • Amounts show as currency, not text.

Assumptions: headers sit in row 1, Order ID is never blank, and Status uses the exact word Cancelled.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking questions first means the macro matches your real rules for blanks, dates and sorting instead of generic guesses.
  • Finding columns by header name keeps the macro working when the export adds or moves a column.
  • Copying to a Cleaned sheet plus the backup and .xlsm steps protects your raw data on the first run.

Use this when you clean the same Excel export by hand every week. Have the header row and a few sample rows ready to paste (remove sensitive data). Works in ChatGPT, Claude or Gemini.

Common edits

  • Ask it to add a button on the sheet that runs the macro with one click.
  • Ask for an extra step that saves the Cleaned sheet as a dated PDF or CSV.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Coding