Excel VBA, hiding columns based on certain conditions

[FaisalAlTawil]

Hey all

I want to make a macro that hides columns if any cell under has a formula, and then copies the same hided column and paste it in new column as hard numbers. in that way I can ensure that all formulas are reserved while all unhide data are as hard coded.


thanks in advance!
Faisal

 

[mse330]

Hi Faisal,

It is possible but what about the structure of your file ? So if column B as one cell with a formula, then column B will be hidden and copied in column H (assuming your data extends to column G) ? Below macro will copy the entire sheet and clear all formulas which might be an even easier method

Sub test()

ActiveSheet.Copy , ActiveSheet
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Value

End Sub