Find Hidden cells in array and enable "Calculate used range" on all non hidden cells

[mrmattmc]

I have been doing a bit of searching and I think, thanks to excels method of handling calculations there is not much to be found on this topic.

I have a sheet that is a summary of a few big sheets of information. The summary sheet holds names in the columns and dates in the rows. The primary workhorse for the sheet is the array formula below, in the columns. With 84 columns and 92 rows I end up with 8148 iterations of this formula. (not PHP)

=IFERROR(HYPERLINK(CELL("address",INDEX('T2'!$B$4:$B$1999,MATCH(1,IF('T2'!$K$4:$K$1999=D$3,IF('T2'!$B$4:$B$1999=$C6,1))),0)),COUNTIFS('T2'!$V$4:$V$1999,$A$1,'T2'!$K$4:$K$1999,D$3,'T2'!$B$4:$B$1999,$C6,'T2'!$E$4:$E$1999,IF($A$3="ALL","*",$A$3),'T2'!$H$4:$H$1999,IF($A$4="ALL","*",$A$4))),0) 

I need to take control of what, when and where excel calculates.

The names displayed change based on a team selection in a data validation list
The dates change in a similar way from a data validation list.

both of the above data sets grow/shrink with the selection made.

With this structure I am using this autofilter vba to essentially hide any row where the date value is zero. One choice yields 38 unique dates, where another yields 92 unique dates.

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheet1
 Application.ScreenUpdating = False
.AutoFilterMode = False
Range("c5:c61").AutoFilter Field:=1, Criteria1:=">0"

I am using this vba to hide the columns where the name field is zero. This data set shrinks and grows like a champ.

If Range("AQ3").Value = "0" Then
Range("aq3,ar3,as3").EntireColumn.Hidden = True
End If

What I would like to do is detect all "hidden" cells in my range. (A1:CK106) then pass the .Calculate code to all cells that are not hidden as needed using Worksheet_Change. Naturally I would have the entire workbook just set to manual calculation so I could fire the calculate command as needed. Been toying with the idea of maybe changing the "status" of the hidden cells to "Data Tables" and setting the workbook to calculate automatic except for data tables option.

Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate

I already control calculations for specific events throughout the workbook with

Application.Calculation = xlCalculationManual

Application.Calculation = xlCalculationAutomatic

[CODE][ActiveSheet.Calculate/CODE]

Any help would be appreciated.

[p45cal]

Instead of formulae in cells, I'd have values, calculated by vba using the same logic (or the same formula using Evaluate in vba, or get vba to enter the formula into specific cells (it looks like your formula can be applied to any multiple-cell range, contiguous or not, with the likes of:

rng.FormulaR1C1 = "=IFERROR(HYPERLINK(CELL(""address"", INDEX('T2'!R4C2:R1999C2,MATCH(1,IF('T2'!R4C11:R1999C11=R3C[-4], IF('T2'!R4C2:R1999C2=RC3,1))),0)), COUNTIFS('T2'!R4C22:R1999C22,R1C1, 'T2'!R4C11:R1999C11,R3C[-4],'T2'!R4C2:R1999C2,RC3,'T2'!R4C5:R1999C5,IF(R3C1=""ALL"",""*"",R3C1),'T2'!R4C8:R1999C8,IF(R4C1=""ALL"",""*"",R4C1))),0)"

follow that with converting them to values with the likes of: rng.value = rng.value)) then you'd have total control of which cells get calculated (…well, their values updated) and when.

Your visible range needing updating could be assigned with the likes of:
Set rng = Range("A1:CK106").SpecialCells(xlCellTypeVisible)

[mrmattmc]

I really like the idea of using VBA to do the heavy lifting. I am slowly learning the how's and why's of vba out of the necessity of this project. I am two weeks into vba. I have been able to setup data extraction functions for getting unique data from source columns on three sheets and sorting it to a data sheet with and without criteria. I have also set up functions for clearing all the old data before the extraction runs.

The formula posted above is one of three heavy lifter formulas I use on the sheet. I would need to target the above formula to all columns in range D4:Ci4 where cell D4:Ci4=T1 row range C5:C101

Can you help me understand why you changed the formula?

IF('T2'!R4C11:R1999C11=R3C[-4],

=

IF('T2'!$K$4:$K$1999=D$3

[p45cal]

I've used .formulaR1C1 instead of .formula because that format for the formulae makes them all the same, regardless of the cell that it's used in.
II you go to Option's and go to the Formula section and change the option from A1 to R1C1 then examine the formulae on your sheet, you'll see they're all the same.

[mrmattmc]

I have come across many a reference to the R1C1 formula formats, just never had reason to go there.

Unfortunately your suggestion for how to use the converted formula is still a bit over my head. I'm like a toddler with a machine gun, cant hit a thing.

I would really like to see your idea fleshed out. The performance boost must be nice.

In the meantime, I ended up using a messy solution that still has excel calculating the entire 8k formulas. Now the blanks are just hidden. There is an 8 second lag of "excel is thinking" every time the data set needs recalculated/macros run.

With Sheet1
Dim i As Integer
Dim c, Rng As Range
Application.ScreenUpdating = False
Rows("5:105").Select
    Range("C5").Activate
    Selection.EntireRow.Hidden = False
    For i = 3 To 3
        Set Rng = Range(Cells(1, i), Cells(107, i).End(xlUp)).SpecialCells(xlCellTypeVisible)
        For Each c In Rng
            If c = 0 And c <> "" Then c.EntireRow.Hidden = True
        Next c
    Next i

Dim cl As Range, rTest As Range
     
    Set rTest = Range("d102", Range("d102").End(xlToRight))
    For Each cl In rTest
        If Not cl.Value > 0 Then
            cl.EntireColumn.Hidden = True
        End If
    Next cl

As every function I consider is a learning curve I have to weigh the cost in gray hairs against the benefit of the solution.

[p45cal]

before we get too deeply into this, if calculation is set to manual does the following line (or something like it) execute quickly if there are few visible rows and/or columns?:
Worksheets("Sheet1").range("A1:CK106")..SpecialCells(xlCellTypeVisible).Cal culate
?

If not, then could you attach a file, redacted if sensitive?

[mrmattmc]

I like it. I need to go in and un-weave the functions and formulas. Just about every function manages the calculate. So I need to go take some of that control out. So when I set it to manual and then change the displayed cells my functions don't just turn it back on.

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic
Activesheet.calculate

I'm guessing the extra period before SpecialCell... was a typo?

Worksheets("Sheet1").Range("A1:CK106").SpecialCells(xlCellTypeVisible).Calculate

[mrmattmc]

The function appears to be working fine.

I think I may be caught in a catch 22.

I use a dynamic date range to populate the sheet. Sometimes the range is 32 rows, sometimes 101. These are a result of formulas and data validation lists. The same time the dates are being populated the 8000 formulas are doing their thing.

So looks like I need to replace this formula with a function.

=IF($A$2=Data!$AB$2,Data!$BT1,IF($A$2=Data!$AB$3,Data!$BV1,IF($A$2=Data!$AB $4,Data!$BX1,IF($A$2=Data!$AB$5,Data!$BZ1,IF($A$2=Data!$AB$6,Data!$CF1,IF($ A$2=Data!$AB$7,Data!$CB1,IF($A$2=Data!$AB$8,Data!$CD1)))))))

A2 is a data validation drop down list. This shows names
DataAB2 is a name
DataBT1 is a date that is dynamically adjusted on the Data sheet with another formula and a filter function.

All 94 cells, C5:C101 have this formula populating them.

I might be able to put together a generic sample file but will need some time to do so. I will have to create three sheets of false data.

[mrmattmc]

Managed to revamp the ordering and insert some targeted calculation statements. Along with the calculate visible idea the load on the system is negligible. The optimization of the workbook is complete. Smaller queries take 1 second to complete and the most complex takes about 2.5

Thanks much. Considering this solved.