Excel VBA: Widening column to fill page margins

[Lazer]

How do I expand the last column (i.e. the right-most column) "G" in m
worksheet to have its width go until the right margin

Columns("G:G").Select
Selection.ColumnWidth = ????

Thanks very much!
Laze

 

[Tom Ogilvy]

Keep expanding it in small increments until the pagebreak moves before it.

Or calculate the width of the page and then calculate the width across
columns A to G and add the positive difference.

 

[Lazer]

Sorry - my question wasn't clear. What is the *syntax* to do tha
(option B)?

Thanks

 

[Tom Ogilvy]

this gets close, but needs more work.

You can play with it and see if you can get it closer to production quality.
You have to set the columnwidth using the columnwidth command. This is
dependent on the default font and does not translate to any measurable unit
that I am aware of. Also, columnwidth does not increment continuously. So
if you assign a columnwidth, the actually columnwidth may only be close.

Anyway, take a look and see what you can come up with (someone else may have
a production quality solution).

Function FindDelta()
Dim cWidth As Single, c1Width As Single
Dim delta As Single
cWidth = Columns(7).ColumnWidth
delta = 0.001
delta1 = delta
Columns(7).ColumnWidth = cWidth
c1Width = Columns(7).ColumnWidth
cnt = 0
Do While c1Width = cWidth
cnt = cnt + 1
delta = delta + delta1
Columns(7).ColumnWidth = cWidth + delta
c1Width = Columns(7).ColumnWidth
If c1Width <> cWidth Then
' Debug.Print cnt, Format(delta, "0.0000"), cWidth, c1Width
FindDelta = delta
End If
' If cnt Mod 25 = 0 Then _
' Debug.Print cnt, Format(delta, "0.0000"), cWidth, c1Width
Loop
End Function

Sub CalcIt()
shwidth = 8.5 * Application.InchesToPoints(1)
lmarg = ActiveSheet.PageSetup.LeftMargin
rmarg = ActiveSheet.PageSetup.RightMargin
prntWidth = shwidth - (lmarg + rmarg)

AFWidth = Columns("A:F").Width
If AFWidth > prntWidth Then
MsgBox "Column G won't fit"
Exit Sub
End If
gWidth = Columns(7).ColumnWidth
GWidthp = Columns(7).Width
delta = FindDelta()
Columns(7).ColumnWidth = gWidth + delta
inc = Columns(7).ColumnWidth - gWidth
incp = Columns(7).Width - GWidthp
numinc = Int((prntWidth - AFWidth) _
/ incp) * incp
Debug.Print inc, numinc - 1, numinc, (inc) * (numinc - 1)
Columns(7).ColumnWidth = inc * (numinc - 1)


End Sub