Submitted By: Kent Finkle

Selects all the formula cells found in the used range of a worksheet.

Visual Basic
Edit|Remove
$comments = @'
Script name: Select-Formulas.ps1
Created on: Friday, September 14, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
Select All the Formula Cells in the Used Range of a Sheet
'@
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers() 
}
#-----------------------------------------------------
 
$xlCellTypeFormulas = -4123
 
$xl = new-object -comobject excel.application
$xl.Visible = $True
 
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
 
$ws.cells.item(1,1) = 1
$ws.cells.item(2,1) = 2
$ws.cells.item(3,1) = 3

$ws.cells.item(1,2) = 4
$ws.cells.item(2,2) = 5
$ws.cells.item(3,2) = 6

$ws.cells.item(1,3) = "=RC[-2]+RC[-1]"
$ws.cells.item(2,3) = "=RC[-2]+RC[-1]"
$ws.cells.item(3,3) = "=RC[-2]+RC[-1]"
 
$r = $ws.UsedRange
$r = $r.SpecialCells($xlCellTypeFormulas)
$a = $r.Select()
 
$a = Release-Ref($r)
$a = Release-Ref($ws)
$a = Release-Ref($wb)
$a = Release-Ref($xl)