Looping Through Excel Filtered Range.

Question

Hi all,

Having got the .AutoFilter to select the correct data for me.....  I now want to be able to loop through just the display rows.

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

I can loop through all 12000 rows and then action on visible rows using the following loop:

For ($row = 2; $row -le (($ws.usedRange.rows).count)) {
  If ($ws.Rows.Item($row).Hidden -eq $false) { 
    Write-Host "Found Row: $row"
  }
  $row ++
}

But this is not very efficient.  Can anybody make a suggestion on how to loop through just the visible rows?  I was thinking of using a range but I'm not sure how this would work.

I know someone out there will have an answer!  

Thanks in advance,

Simon.

Answer 1

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

---

HTH, Bernie

Answer 2

Hi Bernie, 

Thanks for the response.... I've been trying to convert this to powershell... without success :(

I've tried: 

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

I'm sure there will be someone out there who can convert the ForEach loop for me!.

Thanks,

Simon.

Answer 3

Take a look at the ForEach-Object cmdlet:

http://technet.microsoft.com/en-us/library/ee176828

I'm not a powershell user - perhaps you can find help here:

http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/threads

---

HTH, Bernie

Answer 4

Thanks Bernie,

I'm ok with the ForEach-object cmdlet... it's just getting the conversion from VBA to into Powershell as there are some quirks around how things are referenced.

I re-raise the question in the Powershell forum.... as you've given me a VBA answer... which I wasn't clear about at the outset... I'll mark this as answered.

Thanks,

Simon.

Answer 5

Wow....I had no idea such a method (Intersect) existed....this will save me A LOT of code.

Thank you!