Column width

[Guest]

I have a sheet that connect to an access database. It updates data on my
sheet from a query in access. It works great. My problem is this: every
time I refresh or update the data, the column resizes to a default or smaller
width. How can I freeze a columns width?

Thanks.

 

[Nick Hodge]

It's really frustrating and there is no real way to stop it. I suspect you
know that you can highlight all the used columns and then double click in
the right split between the last highlighted column header and the first
unhighlighted one, (You cursor will change to an east/west arrow when you
are in the right place). This autofits the columns to the widest entry in
the column(s). This works in Access too, but Access only sizes to the
largest data in view, Excel's works better on all column data, whether
visible or not

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Debra Dalgleish]

Right-click on a cell in the table
Choose Data Range Properties
Remove the check mark from 'Adjust column width'
Click OK

 

[Nick Hodge]

Debra

I never knew that..phew, that'll save me a few clicks... (learn something
every day),thanks
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Guest]

That does not work. When I refresh data, it still resizes the whole column.

 

[Debra Dalgleish]

Nick,

Glad I could help! I'm sure you have better things to do with your time.

Debra

 

[Debra Dalgleish]

What version of Excel are you using?
Are you refreshing the data manually or programmatically?
Is there anything else on the worksheet?

 

[Nick Hodge]

Debra

I knew the dialog was there, it was just I had gone blind to that option. I
frequently use the 'fill formulas' checkbox.

I need Ron's new glasses ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Guest]

Hi there. I've had a lot of trouble with one as well. In this case, are you
getting new columns of data when you refresh? If so, then you are getting
column size from some other setting somewhere (I never have figured that
out). The only way I've been able to solve this is by progromatically
adjusting column width on a refresh. Of course I'm terribly lazy so there
may actually be a better way but once I solved it, I just use that code...

If you are familiar with code, then you need to create a class module for the
query and then use the AfterRefresh event on the query table. Have it run a
function that adjusts the column width.

I would imagine that other people in this group would have a lot better code
than I do, but maybe this will be a start.

If you are not getting new columns then you should be able to select the
"Preserve Cell Format" check box off the "Table Settings" right click menu.

Hope it helps,
Nathan