Updating charts when Manual Calculation enabled
 

Hi. In Excel 2003, I have a workbook in which I've set up several sumproduct
formulae to be utilized in a chart. Additionally I have a macro that will
hide columns for which the sumproduct = 0. There was a delay when adding new
data to the source page, so I turned on Manual calculate which immediately
corrected that issue. However, when I run the macro, which begins with a
command to calculate, the chart will not update to show the new columns of
data until I run the macro a second time, even though the column is no longer
hidden in the data page. I've included the code below. Is there a way to
force the chart to update in the macro? Thanks, in advance.

Sub hideconfigcolumns()
Sheets("Machine Types Charted").Select
Calculate
Columns("m:as").Select
Selection.EntireColumn.Hidden = False
With ActiveSheet
For i = 13 To 50
If Cells(16, i) = 0 Then
Cells(1, i).EntireColumn.Hidden = True
End If
Next
End With
End Sub

Updating charts when Manual Calculation enabled
 

Just add another calculate command after you unhide the cells. Otherwise, the
chart never receives the signal to look for new data.

Sub hideconfigcolumns()
Sheets("Machine Types Charted").Select
Calculate
Columns("m:as").Select
Selection.EntireColumn.Hidden = False
With ActiveSheet
For i = 13 To 50
If Cells(16, i) = 0 Then
Cells(1, i).EntireColumn.Hidden = True
End If
Next
Calculate
End With
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"hnyb1" wrote:

Hi. In Excel 2003, I have a workbook in which I've set up several sumproduct
formulae to be utilized in a chart. Additionally I have a macro that will
hide columns for which the sumproduct = 0. There was a delay when adding new
data to the source page, so I turned on Manual calculate which immediately
corrected that issue. However, when I run the macro, which begins with a
command to calculate, the chart will not update to show the new columns of
data until I run the macro a second time, even though the column is no longer
hidden in the data page. I've included the code below. Is there a way to
force the chart to update in the macro? Thanks, in advance.

Sub hideconfigcolumns()
Sheets("Machine Types Charted").Select
Calculate
Columns("m:as").Select
Selection.EntireColumn.Hidden = False
With ActiveSheet
For i = 13 To 50
If Cells(16, i) = 0 Then
Cells(1, i).EntireColumn.Hidden = True
End If
Next
End With
End Sub

Updating charts when Manual Calculation enabled
 

Wow! Too easy :-) Thank you very much!

"Luke M" wrote:

Just add another calculate command after you unhide the cells. Otherwise, the
chart never receives the signal to look for new data.

Sub hideconfigcolumns()
Sheets("Machine Types Charted").Select
Calculate
Columns("m:as").Select
Selection.EntireColumn.Hidden = False
With ActiveSheet
For i = 13 To 50
If Cells(16, i) = 0 Then
Cells(1, i).EntireColumn.Hidden = True
End If
Next
Calculate
End With
End Sub

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"hnyb1" wrote:

Hi. In Excel 2003, I have a workbook in which I've set up several sumproduct
formulae to be utilized in a chart. Additionally I have a macro that will
hide columns for which the sumproduct = 0. There was a delay when adding new
data to the source page, so I turned on Manual calculate which immediately
corrected that issue. However, when I run the macro, which begins with a
command to calculate, the chart will not update to show the new columns of
data until I run the macro a second time, even though the column is no longer
hidden in the data page. I've included the code below. Is there a way to
force the chart to update in the macro? Thanks, in advance.

Sub hideconfigcolumns()
Sheets("Machine Types Charted").Select
Calculate
Columns("m:as").Select
Selection.EntireColumn.Hidden = False
With ActiveSheet
For i = 13 To 50
If Cells(16, i) = 0 Then
Cells(1, i).EntireColumn.Hidden = True
End If
Next
End With
End Sub