Excel VBA calculate and set column width

Question

I'm trying the center the range from B:AE in the center of the screen by adjusting the width of column A.

I'm able to change the width by adding the width as number instead of using AdjColWidth

Sub TestWH()

'Get widths
WinWidth = ActiveWindow.UsableWidth
RangeWidth = ActiveSheet.Range("B1:AE1").Width
AdjColWidth = WinWidth - RangeWidth / 2

'If less than 4 then set to 4
If AdjColWidth < 4 Then
    Range("A:A").ColumnWidth = 4
Else
    Range("A:A").ColumnWidth = AdjColWidth
End If

End Sub

AdjColWidth must be less than or equal to 255. I'm assuming your getting an error, and I'm assuming it is AdjColWidth is greater than what is allowed. — Demetri, Commented Dec 23, 2015 at 16:53
@Demetri you nailed it right on the head. Here is a link to the limits for excel support.office.com/en-my/article/… — Sorceri, Commented Dec 23, 2015 at 16:56
Shouldn't AdjColWidth = WinWidth - RangeWidth / 2 be AdjColWidth = (WinWidth - RangeWidth) / 2 ? I mean I know there are limits, but on a 1080p screen a column nearly expands to the whole width at 100% zoom - the number produced by the second formula should be quite a bit smaller (but as you guys say you should check that it's less than 255 before setting it). — user1641172, Commented Dec 23, 2015 at 17:01
@NickDewitt yes otherwise the division happens before the subtraction — Sorceri, Commented Dec 23, 2015 at 17:03
It's also worth noting that Activewindow.UsableWidth is in points, whereas one unit of columnwidth is equal to the width of one character in the Normal style — user1641172, Commented Dec 23, 2015 at 17:09

user1641172user1641172 · Accepted Answer · 2015-12-24 09:56:41Z

3

Based on my comments, something like below should work. I worked out the ratio of points to columnwidth directly in the code, as this could change based on the font size of the normal style.

Sub TestWH()

'Get widths
WinWidth = ActiveWindow.UsableWidth

'work out the ratio between pixels and columnwidth
ratio = ActiveSheet.Columns(1).ColumnWidth / ActiveSheet.Columns(1).Width

RangeWidth = ActiveSheet.Range("B1:AE1").Width

'work out the size in columnwidth values
adjcolwidth = ((WinWidth - RangeWidth) / 2) * ratio

'If less than 4 then set to 4
If adjcolwidth < 4 Then
    Range("A:A").ColumnWidth = 4
ElseIf adjcolwidth < 255 Then
    Range("A:A").ColumnWidth = adjcolwidth
Else
    'what do you want to do if it's greater than 255?
End If

End Sub

answered Dec 24, 2015 at 9:56

user1641172

Sign up to request clarification or add additional context in comments.

2 Comments

OyvPet Over a year ago

This look promising. Will give it a try and report Back as soon as i get some free time and all the christmas presents are opened. Happy christmas holiday everyone :)

OyvPet Over a year ago

It worked great. The last thing to figure out is to run this every time a user changes the active window size or use zoom. Im not actually sure its possible to make this a realtime thing.

Collectives™ on Stack Overflow

Excel VBA calculate and set column width

1 Answer 1

2 Comments

Your Answer

Post as a guest

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related