Loop Through Visible Columns within Range

[aoifew]

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

 

[Akuini]

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

Dim c As Range
For Each c In Range("A1", Cells(1, Columns.count).End(xlToLeft)).SpecialCells(xlCellTypeVisible)

'Debug.Print c.Address

Next

 

[aoifew]

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

 

[jasonb75]

Why not do it all at once with something like

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 = [COLOR="#FF0000"]"Your Calculation"[/COLOR]
    End If
Next

 

[aoifew]

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

 

[aoifew]

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

 

[jasonb75]

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.

Cells(600, i).Value = "Your Calculation"

 

[aoifew]

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?

 Dim rng As Range

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

[FONT=Times New Roman][/FONT]
      Sheets("Sheet1").Range("f600")= lColorCounter
[FONT=Times New Roman][/FONT]

[/COLOR][/SIZE][/FONT]

 

[jasonb75]

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.

 

[aoifew]

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