Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Loop Through Visible Columns within Range

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Loop Through Visible Columns within Range

    Hi
    Iíve managed to use VBA to hide columns in a worksheet that arenít equal to a cell value, leaving a number of columns visible within the larger range.

    LastColumn = 300 'Last Column
    For i = 5 To LastColumn 'Looping through each Column, from column E(5)
    'Hide all the columns with the values as 0 in Row 2
    If Cells(2, i) <> Range("C1") And Cells(2, i) <> "" Then Columns(i).EntireColumn.Hidden = True
    Next

    What I need a pointer on is how to select each column that remains visible within the range in order to loop through the columns individually to tally up some data below each one(I think Iíve sorted code for this). There will on occasions be more than one visible column within the range.

    Thanks in advance

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,058
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    This will loop through each visible column from column A to the last colomn with data.
    You can check it by uncommenting this line: Debug.Print c.Address

    Code:
    Dim c As Range
    For Each c In Range("A1", Cells(1, Columns.count).End(xlToLeft)).SpecialCells(xlCellTypeVisible)
    
    'Debug.Print c.Address
    
    Next

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Thank you, that was great. I'm now struggling to adapt this to looking at the value in cell C1 (as per my code in my original post) and then to perform my calculations for each column. The results of the calculations have to be inserted into the same column much further down the column away from the data.
    Cheers

  4. #4
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,653
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Why not do it all at once with something like
    Code:
    LastColumn = 300 'Last Column
    For i = 5 To LastColumn 'Looping through each Column, from column E(5)
        'Hide all the columns with the values as 0 in Row 2
        If Cells(2, i) <> Range("C1") And Cells(2, i) <> "" Then
            Columns(i).EntireColumn.Hidden = True
        Else
            Cells(Rows.Count, i).End(xlUp).Offset(1).Value = "Your Calculation"
        End If
    Next

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Thanks Jason, this has progressed matters. I've moved your line above 'Else' as I want to complete calculations on the visible columns. It inserts "Your Calculation" at what appears to be the bottom of the column where data stops but I need it being entered in a fixed row (e.g row 600) in each column. Would appreciate your further input

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Apologies, I’m getting really confused. By moving the ‘calculation’ line above ‘Else’,it’ll insert the words in a whole host of cells. Equally in the currentposition, it’s inserting all over the place as well. I only want the insertionsat a fixed row well below all data for only columns where the values in thespecific rows are equal to the value in C1. Thanks


  7. #7
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,653
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Not sure how you're making that work, anything above else will be hidden?

    Anyway, changing the line that I added as below will insert your calculation into row 600 of the column designated by the variable i.
    Code:
    Cells(600, i).Value = "Your Calculation"

  8. #8
    New Member
    Join Date
    Jun 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Yes, that’s works a treat. My ultimate goal is to ‘calculate’ the number of several differentcolours in each of the visible columns and insert the respective totals in row600, 601 etc depending upon number of colours.

    I’m using conditional formatting that relies on row valuesso I’m unable to use CountIf within a formula. The DisplayFormat property can’t be used in UDF and this property is required to countcolours associated with conditional formatting. Hence the need for VBA. Is it possibleto incorporate the following VBA (it works in it’s current form) into Jason’scode so it updates the relevant rows of the visible columns?

    Code:
     Dim rng As Range
    
    
        Dim lColorCounter As Long
    
        Dim rngCell AsRange
    
        'Set the range
    
        Set rng =Sheets("Sheet1").Range("f2:f599")
    
        'loop through eachcell in the range
    
        For Each rngCellIn rng
    
            'Checking redcolor
    
            IfCells(rngCell.Row, rngCell.Column).DisplayFormat.Interior.Color = RGB(255, 0,0) Then
    
               lColorCounter = lColorCounter + 1
    
            End If
    
        Next
    
    
          Sheets("Sheet1").Range("f600")= lColorCounter
    Code:
    
    

  9. #9
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,653
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    Quote Originally Posted by aoifew View Post
    I’m using conditional formatting that relies on row valuesso I’m unable to use CountIf within a formula.
    Not true, you don't count the colours, you count the values using the same rule as the conditional formatting. You don't need any vba for this at all.

  10. #10
    New Member
    Join Date
    Jun 2019
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop Through Visible Columns within Range

    I hope I’m wrong as it’ll make things a whole lot easier.
    An example of one condition is below.
    =(NOW()+30)-$E7
    So for each row we have a course with a specific expiry period. The number of days for that course is kept in, this example, cell E7. The next conditional format formula relates to E8 which may have a different expiry period. There are approx. 50 different courses with various expiry periods.
    Perhaps I can’t see the wood from the trees but I feel this isn’t possible without the use of VBA?
    Thanks for all your help to date Jason

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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