Macro to set specific column width for columns with data

[samc22]

Hi,

I'm building an excel model where users may enter data by column along a time scale with projects by row.

I'd like to summarise the table for printing by compacting the columns without data to the width of the header (without hiding them), while setting the width of the rows with data to a predefined width.

p.s. I'm not the greatest with code so any help would be awesome!

Thanks very much,

Scott

 

[AMAS]

Hi Scott,

Maybe something like this:

Sub AutosizeWorksheet()
Dim Cl As Long, Rw As Long, LastCl As Long, LastRw As Long
LastCl = ActiveSheet.UsedRange.Columns.Count
LastRw = ActiveSheet.UsedRange.Rows.Count
For Cl = 1 To LastCl
    If Cells(LastRw, Cl) = "" Then
        Columns(Cl).AutoFit
    Else: Columns(Cl).ColumnWidth = 30
    End If
Next Cl
End Sub

You can change the number 30 to whatever you want the new width to be.

abousetta