Using Powershell to loop through an Excel Filtered Range.

Question

_{Tuesday, July 24, 2012 2:45 PM}

Hi

I have a powershell script which interacts with an Excel Spreadsheet.  Having got the .AutoFilter to select the correct data for me.....  I now want to be able to loop through just the displayed rows.

So, I have 12000 rows in the spreadsheet, the filter is displaying (for Example) rows,  100, 1200, 2459, 5843, 11995.

Bernie provided the VBA code below which will do what I need, but I've been trying to convert the following Excel VBA code to powershell... without success :(

In VBA, you would use

Dim rngC As Range
Dim ws As Worksheet
Set ws = ActiveSheet
For Each rngC In Intersect(ws.UsedRange, ws.Range("A:A").SpecialCells(xlCellTypeVisible))
MsgBox "Found Row: " & rngC.Row
Next rngC

I've tried: 

Foreach ($rng in Intersect($ws.UsedRange,$ws.Range("A:A").SpecialCells(xlCellTypeVisible))) {
MsgBox "Found Row: " & $rng.Row
$row ++
}

I think it's just a syntax issue, I'm sure there will be someone out there who can convert the ForEach loop for me!.

Thanks,

Simon.

All replies (10)

_{Wednesday, July 25, 2012 2:13 PM ✅Answered | 1 vote}

Intersect — and Union — is a problematic method, I have never been able to use it through PowerShell. The good news is that, in this case, there is no need for it, simply grab the first column of the filtered, or used, range, retrieve the visible cells and omit the first cell, AutoFilter assumes there is a header row, if that is not your case, I would recommend you insert one, otherwise you may have unexpected results. Try this:

$xlCellTypeVisible = 12
$cells = $ws.UsedRange.Columns.Item(1).SpecialCells($xlCellTypeVisible) |
  Select-Object -Skip 1
if ($cells) {
  foreach ($cell in $cells) {
    $cell.Row
  }
}

_{Tuesday, July 24, 2012 4:11 PM}

I don't have your data set, but this may help:

foreach($rng in $xl.Intersect($ws.UsedRange, $ws.Range("A:A").SpecialCells(0xc))) {

#msgbox code#Simplistic messagebox code:[System.Windows.Forms.MessageBox]::Show("Current Row: $row", "Current Row")
$row++

}

Looked up xlCellTypeVisible here: http://nerds-central.blogspot.com/2007/02/excel-constant-definitions-for-vbscript.html

G. Samuel Hays

_{Tuesday, July 24, 2012 4:52 PM}

Hi thanks for the reply. 

Unfortunately, this still does not work for me.  So to help me understand what the syntax is supposed to do:

# Create a range for Column B used to filter for email addresses.

$rngEmails = $ws.Cells.Item(1,2).EntireColumn

......

#Other code

......

# $rng  - This is a range object which should reference each visible row from the applied filter.

# $ws.usedrange - This is a range of all used cells on the activeworksheet (specified as $ws)

# $ws.range - Not sure what this is meant to refer to.



foreach($rng in $xl.Intersect($ws.UsedRange, $ws.Range("A:A").SpecialCells(0xc))) {


  # Debug Code:
  write-host "Current Row: " $rng.row

  $row = $rng.row
}

Here is a sample dataset.  I have filtered on column B (Column B based on an email address of '[email protected]' ) This filter has returned rows.  100, 1200, 2459, 5843, 11995.  I want to retrieve the row numbers for the displayed rows.... using Powershell.

Row: | Column A  |  ColumnB



100  | LocationA |  [email protected]

1200 | LocationB | [email protected]

2459 | LocationC | [email protected]

5843 | LocationD | [email protected]

11995| LocationE | [email protected]

Thanks you your help.

Simon.

_{Tuesday, July 24, 2012 7:30 PM}

This is definitely not my forte - I only translated the code to syntactically correct & functional powershell. Is it actually giving you an error or not doing what you need?

_{Tuesday, July 24, 2012 8:14 PM}

Here's the error I get when attempting to run the code shown in the previous post.

Cannot find an overload for "Intersect" and the argument count: "2".
At H:\WindowsPowerShell\update_excel_sheet.ps1:229 char:32
+         foreach($rng in $xl.Intersect <<<< ($ws.UsedRange, $ws.Range("A:A").SpecialCells(0xc))) {
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

_{Tuesday, July 24, 2012 8:24 PM}

What version of Excel are you running?

G. Samuel Hays

_{Wednesday, July 25, 2012 7:56 AM}

Win7 Enterprise x32 Office 2007 Pro.  What are you thinking??

_{Thursday, July 26, 2012 7:56 AM}

I managed to stumble up this exact same solution myself.... whereby selecting the used range and applying the specialcells visible method to the range - instead of trying to use the intersect methods.!

Thank you, it is exactly what I was looking for.

Simon.

_{Thursday, July 26, 2012 8:23 AM}

You're welcome.

_{Friday, December 28, 2012 12:56 AM}

 Great stuff.  The example made it easy for me to figure out how to create a filter and remove unwanted cells. 

$xlCellTypeVisible = 12
$cells = $worksheet.UsedRange.Columns.Item(1).SpecialCells($xlCellTypeVisible) |
  Select-Object -Skip 1
if ($cells) {
  foreach ($cell in $cells) {
    $cell.delete()
  }
}

Thanks!