Missing Value Auditor: Find Every Blank Cell in Your Required Columns — Instantly

TL;DR: You have a 2,000-row TB where columns A (account code), B (description), and D (CY balance) must be populated. Blank cells break SUMIFS, pivot tables, and cross-references. This macro takes three InputBox clicks — header row, required columns, and scope — then lists every blank on a clickable report. Click any sheet name to jump straight to the empty cell.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook where at least one sheet has tabular data with column headers
• Your data must have a header row — the macro uses it to identify which row labels go with which columns

What the macro does NOT do:

• It does not modify any cells on your original sheets. All output goes to a new “Missing-Values” report sheet.
• It does not fill in the blanks — it tells you where they are. You decide what belongs in each cell.
• It does not check for data validity. An account code of “XYZ-999” will pass if the cell isn’t blank. This macro checks emptiness, not correctness.

Limitations:

• Only checks cells below the header row — anything above is assumed to be titles, firm info, or metadata
• Column letters must be single letters (A through Z). Multi-letter columns (AA, AB, etc.) can be easily added — see Adapt It
• If a sheet has no data below the header row, it’s skipped without error

#The Macro

Option Explicit

Sub MissingValueAuditor()
    ' ── Missing Value Auditor ──────────────────────────
    ' Finds every blank cell in user-specified required
    ' columns and lists them on a "Missing-Values" report
    ' sheet with clickable hyperlinks. Never modifies
    ' the source data — output is a read-only report.
    '
    ' Three InputBox prompts:
    '   1. Select the header row
    '   2. Enter required column letters (e.g., A,C,D)
    '   3. Check all sheets or active sheet only?
    ' ────────────────────────────────────────────────────

    Const OUT_SHEET As String = "Missing-Values"

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim headerRow As Range
    Dim colInput As String
    Dim colsRequired() As String
    Dim checkAll As Boolean
    Dim i As Long, j As Long, r As Long
    Dim ws As Worksheet, wsOut As Worksheet
    Dim rptRow As Long, lastRow As Long, colNum As Long
    Dim colLabel As String
    Dim missing As Long, totalCells As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Input 1: Select the header row ─────────────────
    On Error Resume Next
    Set headerRow = Application.InputBox( _
        "Select the HEADER ROW (the row containing column labels like " & _
        "'Account Code', 'Description', 'CY Balance'):", _
        "Step 1: Header Row", Type:=8)
    On Error GoTo CleanUp
    If headerRow Is Nothing Then GoTo CleanUp

    ' ── Input 2: Required columns ──────────────────────
    colInput = InputBox( _
        "Enter the LETTERS of columns that MUST be populated:" & vbCrLf & _
        "Example: A,C,D  (comma-separated, no spaces required)", _
        "Step 2: Required Columns")
    If colInput = "" Then GoTo CleanUp

    ' Remove spaces and split
    colInput = Replace(colInput, " ", "")
    colsRequired = Split(colInput, ",")

    ' Validate each column letter
    For i = 0 To UBound(colsRequired)
        If Len(colsRequired(i)) <> 1 Or _
           Asc(UCase(colsRequired(i))) < 65 Or _
           Asc(UCase(colsRequired(i))) > 90 Then
            MsgBox """" & colsRequired(i) & _
                   """ is not a valid column letter." & vbCrLf & _
                   "Use single letters only: A through Z.", _
                   vbExclamation, "Invalid Column"
            GoTo CleanUp
        End If
    Next i

    ' ── Input 3: Scope ─────────────────────────────────
    Dim scope As VbMsgBoxResult
    scope = MsgBox( _
        "Check ALL sheets in the workbook?" & vbCrLf & vbCrLf & _
        "  Yes = Check every sheet" & vbCrLf & _
        "  No  = Active sheet only" & vbCrLf & _
        "  Cancel = Quit", _
        vbYesNoCancel + vbQuestion, "Step 3: Scope")
    If scope = vbCancel Then GoTo CleanUp
    checkAll = (scope = vbYes)

    ' ── Create the report sheet ────────────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    Set wsOut = ThisWorkbook.Worksheets.Add( _
        Before:=ThisWorkbook.Worksheets(1))
    wsOut.Name = OUT_SHEET

    ' Headers
    wsOut.Range("A1:D1").Value = Array( _
        "Sheet", "Row", "Column", "Header Label")
    With wsOut.Range("A1:D1")
        .Font.Bold = True
        .Interior.Color = RGB(50, 50, 50)
        .Font.Color = vbWhite
    End With

    rptRow = 2
    missing = 0
    totalCells = 0

    ' ── Build the list of sheets to scan ───────────────
    Dim wssToCheck() As Worksheet
    Dim sheetCount As Long

    If checkAll Then
        sheetCount = ThisWorkbook.Worksheets.Count
        ReDim wssToCheck(1 To sheetCount)
        For i = 1 To sheetCount
            Set wssToCheck(i) = ThisWorkbook.Worksheets(i)
        Next i
    Else
        sheetCount = 1
        ReDim wssToCheck(1 To 1)
        Set wssToCheck(1) = ActiveSheet
    End If

    ' ── Scan each sheet ────────────────────────────────
    Dim sIdx As Long
    For sIdx = 1 To sheetCount
        Set ws = wssToCheck(sIdx)

        ' Skip the report sheet itself
        If ws.Name = OUT_SHEET Then GoTo NextSheet

        ' Find the last row with data in column A
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        If lastRow <= headerRow.Row Then GoTo NextSheet

        ' ── Check each required column ──────────────────
        For j = 0 To UBound(colsRequired)
            colNum = Asc(UCase(colsRequired(j))) - 64

            ' Get the header label for context
            colLabel = CStr(ws.Cells(headerRow.Row, colNum).Value)
            If colLabel = "" Then
                colLabel = "Column " & UCase(colsRequired(j))
            End If

            ' Scan from the row below the header to the last row
            For r = headerRow.Row + 1 To lastRow
                totalCells = totalCells + 1

                If IsEmpty(ws.Cells(r, colNum)) Then
                    ' ── Blank found — write to report ────
                    wsOut.Cells(rptRow, 1).Value = ws.Name
                    wsOut.Cells(rptRow, 2).Value = r
                    wsOut.Cells(rptRow, 3).Value = UCase(colsRequired(j))
                    wsOut.Cells(rptRow, 4).Value = colLabel

                    ' Sheet name becomes a hyperlink to the blank cell
                    wsOut.Hyperlinks.Add _
                        Anchor:=wsOut.Cells(rptRow, 1), _
                        Address:="", _
                        SubAddress:="'" & ws.Name & "'!" & _
                            UCase(colsRequired(j)) & r, _
                        TextToDisplay:=ws.Name

                    missing = missing + 1
                    rptRow = rptRow + 1
                End If
            Next r
        Next j

NextSheet:
    Next sIdx

    ' ── Finalize the report ────────────────────────────
    If missing = 0 Then
        ' Nothing found — clean up and report
        wsOut.Cells(2, 1).Value = "No missing values found."
        wsOut.Columns("A:D").AutoFit
        ActiveWindow.FreezePanes = True

        MsgBox "Checked " & Format(totalCells, "#,##0") & _
               " cell(s) across " & sheetCount & " sheet(s)." & vbCrLf & _
               "No missing values found in the required columns." & vbCrLf & _
               "Headers used from row " & headerRow.Row & ".", _
               vbInformation, "All Clear"
    Else
        ' Sort: by sheet, then column, then row
        wsOut.Columns("A:D").AutoFit
        If rptRow > 3 Then
            wsOut.Range("A2:D" & rptRow - 1).Sort _
                Key1:=wsOut.Range("A2"), Order1:=xlAscending, _
                Key2:=wsOut.Range("C2"), Order2:=xlAscending, _
                Key3:=wsOut.Range("B2"), Order3:=xlAscending, _
                Header:=xlNo
        End If
        ActiveWindow.FreezePanes = True

        ' Summary
        MsgBox "Checked " & Format(totalCells, "#,##0") & _
               " cell(s) across " & sheetCount & " sheet(s)." & vbCrLf & _
               vbCrLf & _
               "Found " & missing & " blank cell(s) in required columns." & _
               vbCrLf & _
               "Report created on '" & OUT_SHEET & "' sheet." & vbCrLf & _
               vbCrLf & _
               "Click any sheet name in column A to jump directly" & vbCrLf & _
               "to the blank cell. Sort and filter the report to" & vbCrLf & _
               "prioritize your review.", _
               vbExclamation, "Missing Values Found"
    End If

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Three prompts, not ten

The macro asks exactly three questions — no more, no less. The header row selection uses Application.InputBox(Type:=8), which lets the user click a row with their mouse instead of typing a row number. The column letters use a plain InputBox because typing “A,C,D” is faster than selecting three separate ranges. The scope is a simple Yes/No/Cancel message box.

Set headerRow = Application.InputBox( _
    "Select the HEADER ROW...", _
    "Step 1: Header Row", Type:=8)

The Type:=8 tells Excel to accept a range selection. The user clicks row 1 and hits OK — no typing, no ambiguity about which row is “the header.”

#Column letters become column numbers

The user types “A,C,D” and the macro splits on commas, strips spaces, and converts each letter to a number with a simple ASCII offset:

colNum = Asc(UCase(colsRequired(j))) - 64

Asc("A") = 65, minus 64 = 1. Asc("C") = 67, minus 64 = 3. This is faster than a lookup table and works for all 26 single-letter columns. The UCase call makes it case-insensitive — the user can type “a,c,d” and it still works.

#Header labels for context

Before scanning a column, the macro reads the cell at headerRow.Row, colNum and stores it as colLabel. This label appears in the fourth column of the report so the user can see which column header each blank falls under.

colLabel = CStr(ws.Cells(headerRow.Row, colNum).Value)
If colLabel = "" Then
    colLabel = "Column " & UCase(colsRequired(j))
End If

If the header cell itself is blank (not ideal, but it happens), the macro falls back to “Column A”, “Column C”, etc. so the report is never missing context.

#IsEmpty, not Len() = 0

If IsEmpty(ws.Cells(r, colNum)) Then

IsEmpty returns True only for truly empty cells — no constant, no formula result, no space character. This is stricter than Len(cell.Value) = 0, which would flag cells containing an empty string from a formula like =IF(A1=0,"",A1). The macro only flags genuinely missing values, not formula outputs that happen to look blank.

If you want to flag zero-length strings too, see the Adapt It section.

#Hyperlinks that click straight to the problem

Each sheet name in column A of the report is a clickable hyperlink:

wsOut.Hyperlinks.Add _
    Anchor:=wsOut.Cells(rptRow, 1), _
    Address:="", _
    SubAddress:="'" & ws.Name & "'!" & _
        UCase(colsRequired(j)) & r, _
    TextToDisplay:=ws.Name

Address:="" means it’s an internal link (not to a web page). SubAddress uses the 'Sheet Name'!CellRef syntax that handles sheet names with spaces and special characters. Click “TB” and Excel jumps to the exact row and column where the value is missing — no scrolling, no hunting.

#The report goes at the beginning

Set wsOut = ThisWorkbook.Worksheets.Add( _
    Before:=ThisWorkbook.Worksheets(1))

The Missing-Values sheet is added as the first tab. This is intentional — it’s a review tool you consult immediately after running the macro. You don’t want to hunt for it in a 20-tab workbook. After you’ve fixed the blanks, delete the report sheet and move on.

Contrast with the hard-coded number detector, which puts its report at the end. That report is a reference you might revisit. This one is actionable — you’re going to fix every item on it, then delete it.

#Sorted for efficient review

wsOut.Range("A2:D" & rptRow - 1).Sort _
    Key1:=wsOut.Range("A2"), Order1:=xlAscending, _
    Key2:=wsOut.Range("C2"), Order2:=xlAscending, _
    Key3:=wsOut.Range("B2"), Order3:=xlAscending

The report is sorted three levels deep: by sheet name first (so all blanks for one sheet are grouped together), then by column letter (so all missing account codes are listed before all missing descriptions), then by row number. This means you can fix all the blanks on one sheet, in one column, working your way down from top to bottom — the most efficient review order.

#Adapt It