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

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.
-Hyperlinks SwitchParameter optional: 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.