locked
Looping Through Excel Filtered Range. RRS feed

  • 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 rngC


    HTH, 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 rngC


    HTH, 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