Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

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

  1. #1
    Join Date
    22nd August 2003
    Posts
    38
    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...

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th June 2003
    Location
    Maryland, USA
    Posts
    368
    Hi Kelly,

    You could try something like this:

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,386
    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.
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th June 2003
    Location
    Maryland, USA
    Posts
    368
    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....

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd August 2003
    Posts
    38
    Thanks guys,

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

    Kelly.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    5th September 2003
    Posts
    3
    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310
    Hi Mehmet,

    Try this example,
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    5th September 2003
    Posts
    3
    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310
    Hi Mehmet,

    Nearly, try this change to handle hidden rows;
    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    5th September 2003
    Posts
    3
    That worked great.
    Thank you Andy and all being on this site!
    -Mehmet

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Copy Visible Cells From Filtered Sheet
    By Sgt. Schultz in forum EXCEL HELP
    Replies: 10
    Last Post: October 23rd, 2007, 09:08
  2. Replies: 5
    Last Post: September 6th, 2007, 08:53
  3. Pass Number Of Visible Filtered Rows
    By khenzel in forum EXCEL HELP
    Replies: 5
    Last Post: August 29th, 2007, 13:24
  4. 1st Visible Cell In Filtered Range
    By JMAN in forum EXCEL HELP
    Replies: 4
    Last Post: December 13th, 2006, 13:56
  5. Working with visible cells of filtered data
    By Esa in forum EXCEL HELP
    Replies: 13
    Last Post: September 8th, 2004, 21:47

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno