Column Width Problem

[Greg Lovern]

I'm trying to copy column widths from a sheet in a workbook that is to
be supplied as needed by the user, to a sheet in a new workbook
created by my Excel macro. In the sample workbook supplied by the user
for development, I find that the destination column widths are
visually much narrower than the source column widths, though the
column width number is the same for both.

I understand that the meaning of column width units comes from the
normal font of the default style of the workbook. But again, in both
cases that's the same -- Arial 10pt. (In Excel 2003, I'm looking at
Format | Style | Normal | Font)

Of course, I've also made sure that zoom is the same for both too --
100%.

For now, I've worked around this problem by doing a copy on the
worksheet object into the new workbook, and deleting what I don't
want. That gives me the same column widths.

But I'd still like to know what could be causing the different column
widths I was seeing. Any suggestions?


Thanks,

Greg

 

[Guest]

The standard font is changed through Tools > Options > General tab > Standard
font. Changing it only affects newly created wkbs. Your assesment as to the
cause is probably correct.

Regards,
Greg

 

[jayray]

I'm trying to copy column widths from a sheet in a workbook that is to
be supplied as needed by the user, to a sheet in a new workbook
created by my Excel macro. In the sample workbook supplied by the user
for development, I find that the destination column widths are
visually much narrower than the source column widths, though the
column width number is the same for both.

I understand that the meaning of column width units comes from the
normal font of the default style of the workbook. But again, in both
cases that's the same -- Arial 10pt. (In Excel 2003, I'm looking at
Format | Style | Normal | Font)

Of course, I've also made sure that zoom is the same for both too --
100%.

For now, I've worked around this problem by doing a copy on the
worksheet object into the new workbook, and deleting what I don't
want. That gives me the same column widths.

But I'd still like to know what could be causing the different column
widths I was seeing. Any suggestions?

Thanks,

Greg

If you do this manually, the way to carry the column width would be
to click the whole column (by clicking on the column letter, say
column C) and copying and pasting that. If you copied C1:C1000, the
column width doesn't get carried over.

In vba, try defining the column as Range("C1").entirecolumn.copy.

 

[Gary Keramidas]

i'm sure there is a shorter way, but i have used something like this before.

Option Explicit
Sub set_column_widths()
Dim arr() As Double
Dim i As Long
Dim x As Long
Dim z As Long
x = 0
For i = 1 To 26
ReDim Preserve arr(0 To i - 1)
arr(x) = Worksheets("Sheet1").Columns(i).ColumnWidth
x = x + 1
Next

For x = LBound(arr) To UBound(arr)
Worksheets("Sheet2").Columns(x + 1).ColumnWidth = arr(x)
Next
End Sub

 

[Greg Lovern]

If you do this manually, the way to carry thecolumnwidth would be
to click the wholecolumn(by clicking on thecolumnletter, saycolumnC) and copying and pasting that. If you copied C1:C1000, thecolumnwidthdoesn't get carried over.

In vba, try defining thecolumnas Range("C1").entirecolumn.copy.- Hide quoted text -

Thanks, but I've tried both of those and still have the same problem.

The destination column *does* get set to the same column width
*number* as the source column. The problem is that even though the two
columns then have the same column width *number*, the destination
column is *visually* much narrower than the source column.

(and both are on 100% zoom.)


Even if I manually set the destination column to the same column width
number as the source column, the destination column is still visually
much narrower than the source column.

Both have the same default font in their normal style -- Arial 10 --
which I understand to be the way that column width units are defined.


Thanks,

Greg

 

[Greg Lovern]

i'm sure there is a shorter way, but i have used something like this before.

Option Explicit
Sub set_column_widths()
Dim arr() As Double
Dim i As Long
Dim x As Long
Dim z As Long
x = 0
For i = 1 To 26
ReDim Preserve arr(0 To i - 1)
arr(x) = Worksheets("Sheet1").Columns(i).ColumnWidth
x = x + 1
Next

For x = LBound(arr) To UBound(arr)
Worksheets("Sheet2").Columns(x + 1).ColumnWidth = arr(x)
Next
End Sub

Thanks, I tried that but still getthe same problem.

The destination column *does* get set to the same column width
*number* as the source column. The problem is that even though the two
columns then have the same column width *number*, the destination
column is *visually* much narrower than the source column.

(and both are on 100% zoom.)


Even if I manually set the destination column to the same column width
number as the source column, the destination column is still visually
much narrower than the source column.

Both have the same default font in their normal style -- Arial 10 --
which I understand to be the way that column width units are defined.


Thanks,

Greg

 

[Guest]

Did you check screen resolution settings? That would make a visual difference.

 

[Greg Lovern]

In case anyone is interested, I've found the problem:

The source sheet had View Formulas turned on (Tools | Options | View |
Window Options | Formulas), which widens the columns. The destination
sheet did not have View Formulas turned on.

I hadn't noticed because that sheet didn't have any formulas. I have
no idea why View Formulas was turned on in the sample provided by the
user.

So the reason my workaround worked was because by doing a copy on the
source worksheet object into the destination workbook, I also copied
over the View Formulas setting.

Greg

 

[Susan]

yes, i was interested - thanks for reporting the solution.
additionally, anybody who searches the newsgroup in the future & finds
your answer will greatly appreciate it, too!
susan