Column Width Decimal Places Rounding

**georgeanaprop** · 07-19-2010, 07:04 AM

Hi

Is there anyway to get excel to not round off when adjusting the column width? For example, I set the column width specifically as such:

    Columns("O:O").Select
            With Selection
            .HorizontalAlignment = xlLeft
            End With
    Selection.ColumnWidth = 8.43

Yet when I check the width afterwards, it is at 8.5

It does this with all the columns where it rounds it off to a single decimal as opposed to two places

Columns need to be formatted to an exact size since they are exported to a formatted text (space deliminated) file format which is very dependant on the column width

Any ideas on how to keep two, and not one decimal place when setting the column width?

Thank You

**Cheeky Charlie** · 07-19-2010, 07:25 AM

The column width is rounded to whatever size is defined by an integer number of pixels, you should be able to set to 8.43 (64 pixels). Can you post the code you're actually using?

**georgeanaprop** · 07-19-2010, 07:47 AM

Thanks

The code itself is pretty lengthy, but this is the last bit of code

I've attached an example workbook as well

       Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Size = 8
    End With
    
    'correct column widths and format
        Columns("A:A").Select
    Selection.ColumnWidth = 6.29
    
        Columns("B:B").Select
    Selection.ColumnWidth = 1.14
    
    Columns("C:C").Select
    Selection.ColumnWidth = 11.29
    
    Columns("D:D").Select
        With Selection
            .HorizontalAlignment = xlRight
        End With
    Selection.ColumnWidth = 5.29
    
    Columns("E:E").Select
        With Selection
            .HorizontalAlignment = xlRight
        End With
    Selection.ColumnWidth = 8.29
    
    Columns("F:F").Select
    Selection.ColumnWidth = 2.29
    
    Columns("G:G").Select
    With Selection
            .HorizontalAlignment = xlLeft
        End With
    Selection.ColumnWidth = 5.43
    
    Columns("H:H").Select
    Selection.ColumnWidth = 6.29
    
    Columns("I:I").Select
        With Selection
            .HorizontalAlignment = xlRight
        End With
    Selection.ColumnWidth = 10.43
    
    Columns("J:J").Select
    Selection.ColumnWidth = 6.29
    
    Columns("K:K").Select
        With Selection
            .HorizontalAlignment = xlLeft
        End With
    Selection.ColumnWidth = 8.43
    
    Columns("L:L").Select
    Selection.ColumnWidth = 9.29
    
    Columns("M:M").Select
        With Selection
            .HorizontalAlignment = xlRight
        End With
    Selection.ColumnWidth = 12.4
    
    Columns("N:N").Select
    Selection.ColumnWidth = 1.29
    
    Columns("O:O").Select
            With Selection
            .HorizontalAlignment = xlLeft
            End With
    Selection.ColumnWidth = 8.43

**Cheeky Charlie** · 07-19-2010, 08:36 AM

Some of these column widths are simply not valid, as I said, the column widths are defined by the width equivalent to the nearest integer number of pixels:
64 pixels = 8.43
65 pixels = 8.57

Any width setting between 8.43 and 8.57 will truncate to 8.43.

I have to say, I'd be very surprised to see space delimited files affected by the (cosmetic) width of the columns. Are you sure these width variations are effecting changes in your output file?

**georgeanaprop** · 07-19-2010, 09:51 AM

Oh ok, i understand what you mean by the pixel integer now and how it truncates to the nearest integer

There is a slight difference in the output file compared to what it was, but I now think that it is not because of the column widths and most likely something else altering the format (although I have stuck to the original format in pretty much every way I can think of). Nonetheless, I will figure it out

I'm going to mark this as solved since the answer is as you stated - it will truncate according to the nearest integer number of pixels.

Thank You!