Column Width Template Question

[metaltecks]

I currently have a report that I export into Excel. The name of the Column is
Company Name. Sometimes the names are small and sometimes big. But the Column
Width always needs to stay at 50 characters wide. So if the name is only 10
characters wide, I still need the remaining 40 to show up in the column.
Since I export this every month, I would like to export this report into a
template that will give me the column size I need.

Is there a way to do this.

Thank you

 

[Dave Peterson]

Columnwidths aren't measured in characters.

=rept("i",50)
is much skinnier than
=rept("W",50)
(in a non-proportional font)

If you use a proportional font (like Courier New), then they would be.)

I think I'd set the width manually or use a macro to make it exactly as wide as
I needed.

Set the width manually and then
Format|Column|width to see the current width that you need to use in the macro.

 

[metaltecks]

I can do this, but my problem is when i click on the column heading to get
rid of the white space, it does. I don't want it to. I want the cells to be
locked with that width.
How can I do that?

 

[Dave Peterson]

Not if you autofit the columns.

Maybe you could create a macro that autofits all the columns and comes back and
fixes that other column.

Something like:

Option Explicit
Sub testme()
With ActiveSheet
.UsedRange.Columns.AutoFit
.Range("c1").EntireColumn.ColumnWidth = 44
End With
End Sub

=====
Or put the longest name possible (or all x's or whatever) in a cell in that
column. Then hide that row.

Then autofitting of that column should respect that (hidden) cell's length.