How to automatically resize excel column widths based on desktop resolution/screen size?

Question

I have an excel workbook that many users work with. This means that there are different monitor resolutions and I wish to know if its possible to automatically resize columns on every sheet to fit the desktop resolution of each computer. Also, columns have different sizes between them so I'd like them to keep proportional when expanding or shortning their width. I'm a basic Visual Basic user but I learn quickly. I'd like the resizing to occur automatically when any user opens the workbook. Any thoughts on how this could be done?

Best regards,

André Bandeira

Answer 1

If you are familiar with VB, this little sub is all what you need. Execute it in your sheet and watch out the output.

Sub Test()

  Dim R As Range

  Set R = ActiveWindow.VisibleRange

  MsgBox _

    "VisibleRange " & R.Address(0, 0) & vbCrLf & _

    "UsedRange   " & ActiveSheet.UsedRange.Address(0, 0)

End Sub

You have to compare the size of this two ranges, and adjust the width of each column. Record a macro to get some code how to shrink or expand a column.

But why want you do that? Why not zoom to show all data?

Sub MakeAllDataVisible()

  If ActiveSheet.UsedRange.Columns.Count > ActiveWindow.VisibleRange.Columns.Count Then

    Intersect(ActiveSheet.UsedRange.EntireColumn, _

      ActiveWindow.VisibleRange.EntireRow).Select

    ActiveWindow.Zoom = True

    ActiveCell.Select

  End If

End Sub

Much more easier.

Andreas.