Vba to conditionally hide/unhide columns

[CROY1985]

I want to automatically hide columns K:Q when the condition of cell C3 is "No Report". C3 is a validation list box with the following options; Weekly, Monthly, Year To Date, Contract To Date, No Report.

There are also date range inputs for the report, so i have a macro which refreshes the whole workbook once these other inputs have been selected.

The code i have is:

Sub Update()
'
' Update Macro
    ActiveWorkbook.RefreshAll
End Sub

What would i need to add to this to have it hide/unhide columns k:q given the condition of C3? (Hide if it is "No Report", Show if it is not "No Report")

Thanks

 

[venkat1926]

try this code

If Range("C3") = "no report" Then
Columns("K:Q").Hidden = True
Else
Columns("K:q").Hidden = False
End If

"no report"-take care of spelling and case both in sheet (C3) and in macro.

 

[CROY1985]

Will give that a try tommorow. Thank you, it look as though it will work a treat. I know what you mean about the spelling issue, so im using data validation boxes for any user inputs and Hopefully, through thorough testing, can be assured of correct formula and vba.

Thanks again.

 

[CROY1985]

Just need some further assistance with the above code. If i want to run it on a specific sheet what do i add to the code, is it just

sheetname.select

after:

If Range("C3") = "no report" Then

I havent got access to excel at the moment so i cant just go in and try it.

Thanks

 

[venkat1926]

worksheets(sheet name within double quotes).activate

another way of not selecting sheet

with worksheets(sheetname within double quotes)
i

If .Range("C3") = "no report" Then .Columns("K:Q").Hidden = True Else .Columns("K:q").Hidden = False End If
end with


'note the dots before range and columns.
</pre>