Skip to main content
SearchCtrl K
Community

Source code

OfficeIMO.NET libraries, PowerShell module, CLI, Studio and browser tools 622 stars

Talk and help

DiscordQuestions, ideas and show and tell Report an issueBugs and feature requests SponsorFund ongoing development

Mode

Background

Search OfficeIMO

Enter a topic, API type, or PowerShell command.

Search the whole siteOpen search page →
Documentation Search all OfficeIMO/
Cmdlet

Clear-OfficeExcelRange

Aliases: ExcelRangeClear
Module PSWriteOffice
Aliases
ExcelRangeClear
Inputs
OfficeIMO.Excel.ExcelDocument
Source
ClearOfficeExcelRangeCommand.cs:18 Edit on GitHub

Clears values, formulas, styles, and range metadata from an Excel worksheet range.

Syntax

Clear-OfficeExcelRange [-All] [-Comments] [-ConditionalFormatting] [-Contents] [-DataValidations] [-Formulas] [-Hyperlinks] [-Merges] [-PassThru] -Range <String> [-Sheet <String>] [-SheetIndex <Int32>] [-Sparklines] [-Styles] [-Values] [<CommonParameters>]
#
Parameter set: Context
Clear-OfficeExcelRange [-All] [-Comments] [-ConditionalFormatting] [-Contents] [-DataValidations] [-Formulas] [-Hyperlinks] [-Merges] [-PassThru] [-Path] <String> -Range <String> [-Sheet <String>] [-SheetIndex <Int32>] [-Sparklines] [-Styles] [-Values] [<CommonParameters>]
#
Parameter set: Path
Clear-OfficeExcelRange [-All] [-Comments] [-ConditionalFormatting] [-Contents] [-DataValidations] -Document <ExcelDocument> [-Formulas] [-Hyperlinks] [-Merges] [-PassThru] -Range <String> [-Sheet <String>] [-SheetIndex <Int32>] [-Sparklines] [-Styles] [-Values] [<CommonParameters>]
#
Parameter set: Document

Examples

Authored help example

Clear cell contents without removing formatting.

Clear-OfficeExcelRange -Path .\Report.xlsx -Sheet Staging -Range B2:D20 -Contents -Hyperlinks -Confirm:$false
Get-OfficeExcelRange -Path .\Report.xlsx -Sheet Staging -Range B2:D20 |
    Select-Object Address, Value, Formula

Removes values and formulas from the selected range and saves the workbook.

Parameters

-All SwitchParameter optional
Clear all supported cell data and range metadata.
-Comments SwitchParameter optional
Clear comments in the selected range.
-ConditionalFormatting SwitchParameter optionalaliases: ConditionalFormats
Clear conditional formatting rules that overlap the selected range.
-Contents SwitchParameter optional
Clear values and formulas.
-DataValidations SwitchParameter optionalaliases: Validation, Validations
Clear data validation rules that overlap the selected range.
-Formulas SwitchParameter optional
Clear formulas.
Clear hyperlinks that overlap the selected range.
-Merges SwitchParameter optional
Clear merged-cell definitions that overlap the selected range.
-PassThru SwitchParameter optional
Emit the object created or changed by the command.
-Range String required
A1 range to clear.
-Sheet String optional
Worksheet name to update. Defaults to the current DSL sheet or the first workbook sheet.
-SheetIndex Int32 optional
Worksheet index (0-based) to update. Defaults to the current DSL sheet or the first workbook sheet.
-Sparklines SwitchParameter optional
Clear sparklines whose target cells overlap the selected range.
-Styles SwitchParameter optionalaliases: Formats
Clear cell style indexes.
-Values SwitchParameter optional
Clear literal cell values.
-Path String requiredposition: 0aliases: FilePath, InputPathsets: Path
Workbook path to update.
-Document ExcelDocument requiredpipeline: True (ByValue)sets: Document
Workbook to update outside the DSL context.

Common Parameters

This command supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable.

For more information, see about_CommonParameters.