Answered by:
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.
- Moved by Bill_Stewart Monday, July 23, 2012 3:26 PM Move to more appropriate forum (From:The Official Scripting Guys Forum!)
Monday, July 23, 2012 3:17 PM
Answers
-
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 rngCHTH, Bernie
- Marked as answer by Mr Chubbsy Tuesday, July 24, 2012 2:45 PM
Monday, July 23, 2012 4:41 PM
All replies
-
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 rngCHTH, Bernie
- Marked as answer by Mr Chubbsy Tuesday, July 24, 2012 2:45 PM
Monday, July 23, 2012 4:41 PM -
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.
Tuesday, July 24, 2012 7:43 AM -
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
Tuesday, July 24, 2012 2:23 PM -
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.
Tuesday, July 24, 2012 2:45 PM -
Wow....I had no idea such a method (Intersect) existed....this will save me A LOT of code.
Thank you!
Thursday, June 27, 2013 2:22 AM