Loop Through Visible Columns within Range

aoifew

New Member
Joined
Jun 23, 2019
Messages
18
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
 
Upvote 0
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
 
Upvote 0
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 = [COLOR="#FF0000"]"Your Calculation"[/COLOR]
    End If
Next
 
Upvote 0
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
 
Upvote 0
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

 
Upvote 0
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"
 
Upvote 0
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

[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]
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top