iOfficeAI/OfficeCLI · error · ArgumentException

AutoFilter range '{afRangeUpper}' overlaps existing table '{

Error message

AutoFilter range '{afRangeUpper}' overlaps existing table '{existingTable.Name?.Value ?? existingTable.DisplayName?.Value}' ({existingTableRef}); tables already include their own autoFilter.

What it means

Thrown by AddAutoFilter after the range is canonicalized. It walks afWorksheet.TableDefinitionParts and, for each table with a Reference, calls RangesOverlap on the upper-cased AutoFilter range and the table's reference. A Table already carries its own <autoFilter>; layering a sheet-level <autoFilter> over the same cells duplicates it and Excel shows a 'found a problem' repair dialog, so the add is rejected up front.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs:763

                @"^\$?[A-Z]+\$?\d+(?::\$?[A-Z]+\$?\d+)?$",
                RegexOptions.IgnoreCase))
            throw new ArgumentException(
                $"Invalid 'range' value: '{afRange}'. Expected a cell range like 'A1:F100' or 'A1'.");
        // Canonicalize inverted input (D5:A1) like the rest of the range family.
        afRange = NormalizeA1Range(afRange);

        // CONSISTENCY(autofilter-table-dup): a Table already owns its own
        // <autoFilter> internally; layering a sheet-level <autoFilter> over
        // the same range produces the duplicate that Excel rejects with a
        // "found a problem" repair dialog. Mirror the T4 overlap check
        // used by AddTable.
        var afRangeUpper = afRange.ToUpperInvariant();
        foreach (var existingTdp in afWorksheet.TableDefinitionParts)
        {
            var existingTable = existingTdp.Table;
            if (existingTable?.Reference?.Value is string existingTableRef
                && RangesOverlap(afRangeUpper, existingTableRef.ToUpperInvariant()))
                throw new ArgumentException(
                    $"AutoFilter range '{afRangeUpper}' overlaps existing table " +
                    $"'{existingTable.Name?.Value ?? existingTable.DisplayName?.Value}' " +
                    $"({existingTableRef}); tables already include their own autoFilter.");
        }

        var wsElement = GetSheet(afWorksheet);
        var autoFilter = wsElement.GetFirstChild<AutoFilter>();
        if (autoFilter == null)
        {
            autoFilter = new AutoFilter();
            // AutoFilter goes after SheetData (after MergeCells if present)
            var mergeCellsEl = wsElement.GetFirstChild<MergeCells>();
            var sheetDataEl = wsElement.GetFirstChild<SheetData>();
            if (mergeCellsEl != null)
                mergeCellsEl.InsertAfterSelf(autoFilter);
            else if (sheetDataEl != null)
                sheetDataEl.InsertAfterSelf(autoFilter);
            else

View on GitHub (pinned to 1ced45e900)

Solutions

1. Use a non-overlapping range for the sheet-level AutoFilter.
2. Rely on the table's built-in AutoFilter instead of adding a sheet-level one over it.
3. Move or shrink the table so the AutoFilter range is disjoint.

Example fix

// before (table occupies A1:F100, AutoFilter covers the same)
handler.Add("/Sheet1", "autofilter", null, new() { ["range"] = "A1:F100" });
// after (filter only the non-table area)
handler.Add("/Sheet1", "autofilter", null, new() { ["range"] = "H1:K50" });

// Pre-check: list tables on the sheet and reject range overlap before Add.
var tableRefs = handler.Query($"/{sheet}/table")
    .Select(n => n.Properties.GetValueOrDefault("ref", ""));
foreach (var tr in tableRefs)
    if (RangesOverlapLocal(afRange.ToUpperInvariant(), tr.ToUpperInvariant()))
        throw new InvalidOperationException($"AutoFilter range overlaps table ref {tr}");

try { handler.Add("/Sheet1", "autofilter", null, props); }
catch (ArgumentException ex) when (ex.Message.Contains("overlaps existing table"))
{
    // use a disjoint range, or rely on the table's own built-in AutoFilter
}

When it happens

Trigger: Call Add type "autofilter" whose range geometrically overlaps a Table's reference on the same sheet.

Common situations: Adding a sheet-level AutoFilter that covers a region already occupied by a ListObject table; defaulting the filter to the whole used range that includes a table.

Related errors

• comment already exists on {cmtRefUpper}. Remove it first bef
• DataValidation sqref '{nr}' overlaps existing validation sqr
• Defined name '{nrName}' collides with the table name '{exist
• Sheet not found: {afSheetName}
• AutoFilter requires 'range' property (e.g. range=A1:F100)

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/0c74c12a8d57d0f3. Report an issue: GitHub.