Using –Match in excel

I needed to match a part of a cell in Excel and delete the row if a match was found. I was looking for server names that had –Old at the beginning or end of the name or -new or zz. or -inactive or -delete or two or more periods (..)


[void]$ws1.Activate()
$last = $ws1.UsedRange.SpecialCells(11).Address($False,$false)
$range1 = $ws1.range("A2:$last" )
$range2 = $ws1.range("A2") </span>

<span style="font-family: Comic Sans MS;"># one-column sort --&gt; works
[void]$range1.sort($range2, $xlAscending) </span>

<span style="font-family: Comic Sans MS;">$used = $ws1.usedRange
$Rng = $ws1.range("A:A")
$oSearch = $Null
$ySearch = $Rng.Find($oSearch)
$zSearch = $ySearch.Row
$R = $zSearch</span>

<span style="font-family: Comic Sans MS;">For ($i = $R; $i -ge 2; $i--)  {
  Write-Progress -Activity "Searching $R Old's..." `
     -PercentComplete ($i/$R*100) -CurrentOperation `
     "$i of $R to go" -Status "Please wait."
      $Rng = $ws1.Cells.Item($i, 1).value()
If ($Rng -match '\bold\b|-new|zz\.|-INACTIVE|-DELETE|\.\.+') {
# Match at Begining and end of word '\b   \b' &lt;-- looking for old? '\bold\b'
# Finds -new or zz. or -inactive or -delete or two or more periods (..)
# in Match a . is the same as *, so you must put a \ before a period \.
# othewise it will catch every charactor.
   Write-Host $Rng
   $Range = $ws1.Cells.Item($i, 1).EntireRow
     [void]$Range.Select()
     [void]$Range.Delete()
     $R = $R - 1
      }
    }
     Write-Progress -Activity "Searching -Old's..." `
  -Completed -Status "All done."
Advertisements

3 thoughts on “Using –Match in excel

  1. Hi mike,
    why don’t you use the powershell syntaxhighlighting of wordpress. I think, the sourcecode is very much better to read with

    $test = [datetime]"2011-02-16"
    
  2. Sorry, I mean

    [ sourcecode language=”powershell”]
    $test = [datetime]”2011-02-16″
    [/sourcecode]

    without the blank before “sourcecode”.

  3. Like

    [void]$ws1.Activate()
    $last = $ws1.UsedRange.SpecialCells(11).Address($False,$false)
    $range1 = $ws1.range("A2:$last" )
    $range2 = $ws1.range("A2")
    
    # one-column sort –> works
    [void]$range1.sort($range2, $xlAscending)
    
    $used = $ws1.usedRange
    $Rng = $ws1.range("A:A")
    $oSearch = $Null
    $ySearch = $Rng.Find($oSearch)
    $zSearch = $ySearch.Row
    $R = $zSearch
    
    For ($i = $R; $i -ge 2; $i–)  {
      Write-Progress -Activity "Searching $R Old’s…" `
         -PercentComplete ($i/$R*100) -CurrentOperation `
         "$i of $R to go" -Status "Please wait."
          $Rng = $ws1.Cells.Item($i, 1).value()
    If ($Rng -match ‘\bold\b|-new|zz\.|-INACTIVE|-DELETE|\.\.+’) {
    # Match at Begining and end of word ‘\b   \b’ <– looking for old? ‘\bold\b’
    # Finds -new or zz. or -inactive or -delete or two or more periods (..)
    # in Match a . is the same as *, so you must put a \ before a period \.
    # othewise will catch every charactor.
       Write-Host $Rng
       $Range = $ws1.Cells.Item($i, 1).EntireRow
         [void]$Range.Select()
         [void]$Range.Delete()
         $R = $R – 1
          }
        }
         Write-Progress -Activity "Searching -Old’s…" `
      -Completed -Status "All done." 
    

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