Macro is only executed when a button is pressed.

Code:

Private Function state(sheet, row, column)

Application.Volatile

state = Sheets(sheet).Cells(row,
WorksheetFunction.VLookup(WorksheetFunction.VLooku p(Sheets("dummy
state").Range("c3"), Sheets("io").Range("alphastate"), 2),
Sheets("io").Range("columns"), column, 0))


End Function

This runs when button is pressed:

Sub hideshowstates()

Application.ScreenUpdating = False

For j = 1 To 2

For i = 6 To 30
If Sheets("Basic Data").Cells(i, j) = "True" Then ' show
in projections sheets
For z = 2 To 28
sht = Sheets("io").Cells(2, z)
If j = 1 Then
col = Sheets("io").Cells(i - 2, z)
Sheets(2).Rows(i + 11).Hidden = False ' show
on percentages sheet
Else
col = Sheets("io").Cells(i + 23, z)
Sheets(2).Rows(i + 36).Hidden = False ' show
on percentages sheet
End If
Sheets(sht).Columns(col).Hidden = False
Next z


Else
For z = 2 To 28 ' hide on projections sheets
sht = Sheets("io").Cells(2, z)
If j = 1 Then
col = Sheets("io").Cells(i - 2, z)
Sheets(2).Rows(i + 11).Hidden = True ' hide on
percentages sheet
For r = 2 To 10
Sheets(2).Cells(i + 11, r).ClearContents
Next r
Else

col = Sheets("io").Cells(i + 23, z)
Sheets(2).Rows(i + 36).Hidden = True ' hide on
percentages sheet
For r = 2 To 10
Sheets(2).Cells(i + 36, r).ClearContents
Next r

End If
Sheets(sht).Columns(col).Hidden = True
Next z
End If

Next i
Next j

End Sub