In VBA, how to set col widths for (say) 7 cols to fit sheet width any screen resolution?

[David Nicholls]

In VBA, I have several sheets with different numbers of columns. I want to set different widths on these columns within a sheet, but they should all exactly fit the sheet width so the user does not have to scroll horizontally. This should work for any screen resolution. Any ideas?

[shg]

You could autofit the column widths, select any row of the data, and then zoom to selection. Does that give the desired result?

[JLGWhiz]

Please Login or Register  to view this content.

would give you 7 equally spaced columns. But if you want different columns to be different widths then you would have to do some math to figure out what to divide the UsableWidth constant by.

[David Nicholls]

Thanks SHG. Unfortunately, the Zoom expands the vertical dimensions (row height) and text size, which I did not want. But the autofit may work if I can set one column width independently. I need to check.

[David Nicholls]

Many thanks JLGWhiz. I think UsableWidth will do the trick.

[shg]

Except it's in pixels, and column widths are not ...

[David Nicholls]

Yes, SHG, when I tried UsableWidth, my columns were many times too wide.

[shg]

If they are all to be the same width,

Please Login or Register  to view this content.

[David Nicholls]

Thanks SHG. On my computer your solution shows a bit of column H as well, but users can manually adjust it out of sight. I will use this method. My original method was diabolically complex, which meant it was *probably* incorrect, and it showed a teensy bit of col H too, which meant it was *definitely* incorrect. The other thing I could do would be to simply set the various ColumnWidths individually to the max expected for that field, and apply word-wrap to text columns like Memo or Description, where the number of chars is unpredictable.