iOfficeAI/OfficeCLI · error · ArgumentException

Invalid 'range' value: '{afRange}'. Expected a cell range li

Error message

Invalid 'range' value: '{afRange}'. Expected a cell range like 'A1:F100' or 'A1'.

What it means

Thrown by AddAutoFilter when the supplied range (trimmed) does not match ^\$?[A-Z]+\$?\d+(?::\$?[A-Z]+\$?\d+)?$. It accepts a single cell or a Cell:Cell rectangle with optional $ anchors; anything else (garbage tokens, whole-column forms like A:A, or a sheet-prefixed ref) is rejected so Excel does not silently open with an invalid <x:autoFilter ref>.

Source

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

        // path naturally.
        if (properties is OfficeCli.Core.TrackingPropertyDictionary afTracking)
        {
            var consumed = properties.Keys
                .Where(k => string.Equals(k, "range", StringComparison.OrdinalIgnoreCase)
                    || Regex.IsMatch(k, @"^criteria\d+\.[A-Za-z]+$"))
                .ToList();
            afTracking.MarkAllConsumed(consumed);
        }

        var afRange = properties.GetValueOrDefault("range")
            ?? throw new ArgumentException("AutoFilter requires 'range' property (e.g. range=A1:F100)");

        // CONSISTENCY(cellref-validate): reject garbage refs (e.g. "BADREF")
        // so Excel doesn't silently open with an invalid <x:autoFilter ref="...">.
        if (!Regex.IsMatch(afRange.Trim(),
                @"^\$?[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}' " +

View on GitHub (pinned to 1ced45e900)

Solutions

1. Use a single cell ("A1") or a rectangle ("A1:F100"), with optional $ anchors.
2. Drop any sheet prefix from the range value (the sheet comes from parentPath).
3. For a whole column, give explicit rows (e.g. "A1:A1048576").

Example fix

// before
handler.Add("/Sheet1", "autofilter", null, new() { ["range"] = "Sheet1!A1:F100" });
// after
handler.Add("/Sheet1", "autofilter", null, new() { ["range"] = "A1:F100" });

var r = properties.GetValueOrDefault("range", "").Trim();
if (!Regex.IsMatch(r, @"^\$?[A-Z]+\$?\d+(?::\$?[A-Z]+\$?\d+)?$", RegexOptions.IgnoreCase))
    throw new InvalidOperationException($"Invalid AutoFilter range '{r}'; use A1 or A1:F100");

static bool IsValidAutoFilterRange(string? r) =>
    r is not null && Regex.IsMatch(r.Trim(),
        @"^\$?[A-Z]+\$?\d+(?::\$?[A-Z]+\$?\d+)?$", RegexOptions.IgnoreCase);

When it happens

Trigger: Call Add type "autofilter" with range set to a malformed value such as "BADREF", "A:A" (whole column, no row), "Sheet1!A1:F100" (sheet prefix), or "A1::F100".

Common situations: Passing a whole-column range (the regex requires a row number); including the sheet name in the range; extra colons; non-A1 tokens from a selector copy-paste.

Related errors

• Invalid anchor: '{chartAnchorStr}'. Expected e.g. 'D2' or 'D
• Invalid anchor: '{oleAnchorStr}'. Expected e.g. 'B2' or 'B2:
• Invalid anchor: '{picAnchorRaw}'. Expected e.g. 'B2', 'B2:E6
• Property 'sqref' (or 'range'/'ref') is required for validati
• AutoFilter requires 'range' property (e.g. range=A1:F100)

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