force calculation of row using VBA

[nomadelement]

Hello,

I am having some serious difficulty getting a row in my sheet to recalculate. I am using VBA to have check boxes hide and unhide rows, then a module to calculate the sum of only the visible rows. The problem is that once I click check boxes (hide / unhide rows) the formula to sum the visible rows will not recalculate and gives an error. I have tried all of the following VBA instructions inserted as the example below. The only instruction which works is "Application.CalculateFullRebuild" however this is a big sheet and that calculation takes a few minutes. I know there is apparently no way to calculate a full rebuild of a active sheet, so I am desperate for any ideas on how to get this a. active sheet. b. row. or c. individual cells to automatically fully recalculate everytime a checkbox is clicked.

Working (but not efficient) solution:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
[6:6].EntireRow.Hidden = False
Else: [6:6].EntireRow.Hidden = True
Application.CalculateFullRebuild (Application.CalculateFull also works)
End If
End Sub


Just in case it helps you the row i need updated is row 11 columns F:K
Other things I have tried and failed with:

ActiveSheet.calculate
Worksheet(1).Calculate
I have also tried many variations on instructions to calculate specific rows / columns, however I will not write everything just in case I did something wrong I still would like to hear your thoughts.

 

[AlphaFrog]

This forces calculation of a specific range F11:K11. If the formulas within that range reference cells outside that range that also have formulas, those may have to be recalculated as well.

Also, the hide row 6 code was changed.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CheckBox1_Click()
    Rows(6).Hidden = [color=darkblue]Not[/color] CheckBox1.Value
    Range("F11:K11").Calculate
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[nomadelement]

This forces calculation of a specific range F11:K11. If the formulas within that range reference cells outside that range that also have formulas, those may have to be recalculated as well.

Also, the hide row 6 code was changed.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CheckBox1_Click()
    Rows(6).Hidden = [COLOR=darkblue]Not[/COLOR] CheckBox1.Value
    Range("F11:K11").Calculate
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Thank you for changing the check box code, that is much cleaner than what I was using. Unfortunately the cells are still not recalculating. The error message "#value" still shows.

 

[nomadelement]

just for clarification, the only ways I can get the cells so far to update are to calculatefull the entire workbook which takes way too long, or to click on the text in the formula of that cell then press enter

 

[AlphaFrog]

Are the cells F11:K11 and what's the formulas in each?

 

[nomadelement]

Are the cells F11:K11 and what's the formulas in each?

I ended up getting it working, heres what I did:

I used your code to hide the cells. I removed my module to create a formula which only calculates hidden cells and used a formula i didnt know before =subtotal(109,RANGE)

Everything now works perfectly!

Thank you for your help, you got me thinking in the right direction... stop making things more complicated than they need to be

 

[AlphaFrog]

Good thing I totally skipped over the part where you said your had a module to calculate.