column width, differences between methods

[Hein]

Hi all,
Yesterday I asked a question if, and how, I can adjust the width of multiple columns to their contents, and then add just a little more width for easier readability.
The code Ollie came up with (thanks again Ollie !) was:

Public Sub AutoFitPlus()
'#
'# declare private variables
'#
     Dim xlsColumn As Excel.Range
     Const clngMargin As Long = 5
'#
'# set columns in selection to autofit
'#
     Selection.Columns.AutoFit
'#
'# add a small margin the the column width for all columns
'# within the selection
'#
     For Each xlsColumn In Selection.Columns
          xlsColumn.ColumnWidth = xlsColumn.ColumnWidth + clngMargin
     Next xlsColumn
End Sub

This works fine, with 1 exception. If, in a large spreadsheet, i start eg in a name column and that name is shorter than the other entries, the new width of the column is relative to this smaller cell.
When I doubble click between the columns (even if I am in the same (short) cell, the column is adjusted to the longest cell in that column.

Is it possible to add this to the code mentioned above?

Thanks Hein

[HSV]

For the whole usedrange.
If there is nothing in the entirecolumn, the columnwidth will not changed.
All the other columns = autofit + 5.

Sub hsv()
Dim col As Range
For Each col In ActiveSheet.UsedRange.Columns
 With col
  If Cells(1, col.Column) <> "" Or Range(Cells(1, col.Column), Cells(Rows.Count, col.Column).End(xlUp)).Rows.Count > 1 Then
    .AutoFit
    .ColumnWidth = .ColumnWidth + 5
   End If
  End With
 Next col
End Sub

[Hein]

Harry,

Sorry for my delayed answer, but I am glad you responded.

Your solution is exactly what I was looking for. Thank you for that
This will save me a lot of tedious work, and time

Again, thanks

Hein