iOfficeAI/OfficeCLI · error · ArgumentException

AutoFilter requires 'range' property (e.g. range=A1:F100)

Error message

AutoFilter requires 'range' property (e.g. range=A1:F100)

What it means

Thrown by AddAutoFilter when the properties dictionary contains no "range" key. Unlike validation (which accepts sqref/range/ref aliases), AutoFilter accepts only "range", so the absence of that single key triggers the error.

Source

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

        // CONSISTENCY(tracking-rebind): the criteriaN.OP loop below iterates
        // properties via foreach over the static Dictionary<,> type, which
        // bypasses TrackingPropertyDictionary's comparer. Mark every
        // criteriaN.OP key (and `range`) as consumed up-front so they
        // don't surface as false unsupported_property warnings. Keys that
        // don't match either pattern fall through to the existing UNSUPPORTED
        // 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)

View on GitHub (pinned to 1ced45e900)

Solutions

1. Add properties["range"] = "A1:F100" (AutoFilter uses the "range" key specifically).
2. Do not rely on sqref or ref aliases here; only "range" is accepted.
3. Pre-validate that the range key is present before calling.

Example fix

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

if (!properties.ContainsKey("range"))
    throw new InvalidOperationException("AutoFilter requires the 'range' key");

static bool HasAutoFilterRange(Dictionary<string,string> p) => p.ContainsKey("range");

When it happens

Trigger: Call Add type "autofilter" with a properties dictionary that has no "range" entry (e.g. only criteria filters, or an empty dict).

Common situations: Forgetting the range argument; supplying sqref/ref expecting them to alias (they do not for AutoFilter); building properties dynamically and dropping range.

Related errors

• Property 'sqref' (or 'range'/'ref') is required for validati
• Chart requires a 'data' property. Use: data="Series1:1,2,3;S
• 'src' property is required for picture type
• Property 'ref' is required for comment
• Property 'formula1' is empty for validation type=list; suppl

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