Find and Delete a Column using an Array in Excel


Here I am looking for column names that I no longer need and deleting the whole column when I find them in my spreadsheet. My spreadsheet has 26 columns so I search from Col 1 to Col 26. Because I am deleting a column, I have to subtract 1 from $i, otherwise it skips a column.

$array = ("CI_ID","SERIALNUMBER","TIER1","TIER2","TIER3","MANUFACTURERNAME","PRODUCT_NAME")

for ($i=1;$i -le 26; $i++) {
$z = $ws1.cells.item(1,$i).value()
$b = $array -contains $z
# If the array ($array) contains the cell value ($z), delete the entire column
If ($b) {
$selection = $ws1.cells.item(1,$i)
[void]$selection.Select()
[void]$selection.entireColumn.delete()
Write-Host "I've deleted $z"
$i = $i - 1
}
} 


Advertisements

One thought on “Find and Delete a Column using an Array in Excel

  1. The script worked for me, with some modifications. However, I receive the following error:
    Exception calling “Select” with “0” argument(s): “Select method of Range class failed”
    At E:\home\scripts\dev\posh\remove-ExcelColumn.ps1:17 char:13
    + [void]$selection.Select()
    + ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

    It still does what I need it to do, so there is that. Thanks for the tip.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s