Hello,

Simple question, but...€¦ what's the answer :)
Somebody knows it ? Thanks !

In a sheet there are many columns, but a few of them hasn't registered data in the rows. When using Cells.EntireColumn.AutoFit it's allways on a size that could be a bit smaller.

Therefore I like to run a macro that runs from first column to last used and check in each column if there is data (in row 2 till last used row). If not, then set the column width on 10.


Regards, Johan

Hi Johan,

Am Mon, 19 Aug 2019 09:44:08 -0700 (PDT) schrieb JS SL:

In a sheet there are many columns, but a few of them hasn't registered data in the rows. When using Cells.EntireColumn.AutoFit it's allways on a size that could be a bit smaller.

Therefore I like to run a macro that runs from first column to last used and check in each column if there is data (in row 2 till last used row). If not, then set the column width on 10.

try:

Sub Test()
Dim myCol As Range

With ActiveSheet
For Each myCol In .UsedRange.Columns
If Application.CountA(myCol) <= 1 Then
myCol.ColumnWidth = 10
Else
myCol.EntireColumn.AutoFit
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Thanks a lot (as usual).
Works as required.
regards, Johan