Automatically resize columns based on new formula result

[Kevin Ward]

I have a sheet that uses a bunch of vlookup formulas that are
referencing a hidden tab. Users select a value from a dropdown and all
data related to that value is pulled from the hidden tab. I am trying
to figure out a way to have excel automatically resize the columns
based on the width of the formula result. I am trying to avoid making
the end users resize everything each time they use it. I think I could
figure out a macro to do this, but then users with certain security
settings may not be able to use it. It is a very crammed sheet as it
is, so I am trying to make it as space efficient as possible. Any
ideas?

 

[Dave Peterson]

You could use a worksheet event.

If you want to try...

Right click on the worksheet tab that should have this behavior. Select view
code and paste this into the code window that you see:

Option Explicit
Private Sub Worksheet_Calculate()
Me.Range("a1,e1,j1").EntireColumn.AutoFit
'or get them all:
me.usedrange.columns.autofit
End Sub

Then back to excel and force a recalculation.

 

[Kevin Ward]

Thanks for the help Dave. It works perfect. Now if I could just ge
our IT department to not have the security settings so high I wouldn'
have to explain to everyone how to change them in order to have macro
work. thanks again for your help