Announcement

Collapse
No announcement yet.

VBA: for..each..next on visible rows in filtered list

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA: for..each..next on visible rows in filtered list



    And for my next dilemma...

    I've got a list that I am applying an AutoFilter to for a single column. I need to then loop through each of the visible rows, conditionally (thus the loop) doing things to each row.

    How do I define a range that contains the visible rows in a filtered list but not the header row?

    By the time I get to this point I have guaranteed that the filter will find at least one row matching the criteria so error trapping for that is not necessary.

    I've thought of a number of ways of doing this, but none of them seem very reliable. This For..Next is going to be nested inside another For..Next which is looping through all the rows in a list on another sheet. Speed could end up being a factor here so I'm looking for a relatively elegant solution.

    Thanks,
    Kelly.

    PS And I've just been told by the colleague that feeds me the reports that I am writing all this VBA for that the format of the reports is likely to change soon. Argh. There goes a week of my life I'll never get back...

  • #2
    Hi Kelly,

    You could try something like this:

    Code:
    Sub EditFilteredRange()
    Dim rng As Range
    Set rng = Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)
       For Each cell In rng
             ' Your Code Goes Here
       Next cell
    End Sub
    assumes that Row 1 is your header row, so it doesn't pick it up.

    Hope this helps

    .....Ralph

    Comment


    • #3
      To get the visible cells excluding the first row just adjust the following column references as needed. The key is that the specialcells only operate in the context of the leadin range.
      Code:
      Sub Macro1()
          Dim v As Range
          Set v = Range("A2:G" & Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Cells
          MsgBox "The cells are " & v.Address
      End Sub
      Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

      Comment


      • #4
        Looks like Derk and I had our fingers on the Post button at the same time. :biggrin:

        Welcome back, Derk - hope you had a great vacation....

        Comment


        • #5
          Thanks guys,

          That's the solution I have gone with and it works like a charm :-)

          Kelly.

          Comment


          • #6
            Hello,
            I partly solved my question without asking it! This site is great.

            The other part is:
            I want to traverse the cells column by column. First all rows in a columns, then move to next column, etc.
            The default of "For Each cell .." is the opposite.

            Thanks in advance.
            -Mehmet

            Comment


            • #7
              Hi Mehmet,

              Try this example,
              Code:
              Sub AlternateForEach()
                  Dim rngX As Range
                  Dim rngY As Range
                  Dim strMsg As String
                  
                  strMsg = "Default Traverse Columns the rows"
                  For Each rngY In Range("a1:c3")
                      strMsg = strMsg & vbLf & rngY.Address
                  Next
                  MsgBox strMsg, vbInformation
                  
                  strMsg = "Alternate Traverse Rows then columns"
                  For Each rngX In Range("a1:c3").Columns
                      For Each rngY In rngX.Cells
                          strMsg = strMsg & vbLf & rngY.Address
                      Next
                  Next
                  MsgBox strMsg, vbInformation
                  
              End Sub
              Cheers
              Andy

              Cheers
              Andy

              Comment


              • #8
                Hi Andy,
                Thanks for quick response.
                I tried your code it works perfect except when there are hidden rows in a range.

                I used your code as follows:
                strMsg = "Alternate Traverse Rows then columns"
                For Each rngX In Application.Selection.SpecialCells(xlCellTypeVisible).Columns
                For Each rngY In rngX.Cells
                strMsg = strMsg & vbLf & rngY.Address
                Next
                Next

                Is there a work around for it?
                -Mehmet

                Comment


                • #9
                  Hi Mehmet,

                  Nearly, try this change to handle hidden rows;
                  Code:
                      strMsg = "Alternate Traverse Rows then columns"
                      For Each rngX In Range("a1:c3").Columns
                          For Each rngY In rngX.SpecialCells(xlCellTypeVisible)
                              strMsg = strMsg & vbLf & rngY.Address
                          Next
                      Next
                  Cheers
                  Andy

                  Cheers
                  Andy

                  Comment


                  • #10


                    That worked great.
                    Thank you Andy and all being on this site!
                    -Mehmet

                    Comment

                    Working...
                    X