From: primed on
Hi,

Can anyone help with code to do the following:

Row E8:AI8 - each cell contains a value 0 to 100, generally a value of 1 to
20. The total of the row is maximum 100.
I would like to adjust the width of the column based on the values for each
column.
The minimum width would need to be 6 so text is still readable.

Thanks in advance

Primed
From: Per Jessen on
Hi Primed

This should do it:

Sub ColumnAdjust()
Set Rng = Range("E8:AI8")
For Each cell In Rng
If cell.Value >= 6 Then
cell.ColumnWidth = cell.Value
Else
cell.ColumnWidth = 6
End If
Next
End Sub

Regards,
Per

"primed" <primed(a)discussions.microsoft.com> skrev i meddelelsen
news:FAB13FFF-37FD-44C7-9D3F-3111DAD7D65A(a)microsoft.com...
> Hi,
>
> Can anyone help with code to do the following:
>
> Row E8:AI8 - each cell contains a value 0 to 100, generally a value of 1
> to
> 20. The total of the row is maximum 100.
> I would like to adjust the width of the column based on the values for
> each
> column.
> The minimum width would need to be 6 so text is still readable.
>
> Thanks in advance
>
> Primed

From: Rick Rothstein on
Why not let VB figure it out for you...

Columns("E:AI").AutoFit

--
Rick (MVP - Excel)



"primed" <primed(a)discussions.microsoft.com> wrote in message
news:FAB13FFF-37FD-44C7-9D3F-3111DAD7D65A(a)microsoft.com...
> Hi,
>
> Can anyone help with code to do the following:
>
> Row E8:AI8 - each cell contains a value 0 to 100, generally a value of 1
> to
> 20. The total of the row is maximum 100.
> I would like to adjust the width of the column based on the values for
> each
> column.
> The minimum width would need to be 6 so text is still readable.
>
> Thanks in advance
>
> Primed

From: primed on
I am not a programmer but that doesnt seem to take into account the values in
row 8.
It will come in handy for something else though.

Cheers
Primed

"Rick Rothstein" wrote:

> Why not let VB figure it out for you...
>
> Columns("E:AI").AutoFit
>
> --
> Rick (MVP - Excel)
>
>
>
> "primed" <primed(a)discussions.microsoft.com> wrote in message
> news:FAB13FFF-37FD-44C7-9D3F-3111DAD7D65A(a)microsoft.com...
> > Hi,
> >
> > Can anyone help with code to do the following:
> >
> > Row E8:AI8 - each cell contains a value 0 to 100, generally a value of 1
> > to
> > 20. The total of the row is maximum 100.
> > I would like to adjust the width of the column based on the values for
> > each
> > column.
> > The minimum width would need to be 6 so text is still readable.
> >
> > Thanks in advance
> >
> > Primed
>
> .
>
From: primed on
Cheers,

Worked great.
I modified slightly.

Sub ColumnAdjust()
Set Rng = Range("E8:AI8")
For Each cell In Rng
If cell.Value >= 1 Then
cell.ColumnWidth = cell.Value * 6
Else
cell.ColumnWidth = 0
End If
Next
End Sub

"Per Jessen" wrote:

> Hi Primed
>
> This should do it:
>
> Sub ColumnAdjust()
> Set Rng = Range("E8:AI8")
> For Each cell In Rng
> If cell.Value >= 6 Then
> cell.ColumnWidth = cell.Value
> Else
> cell.ColumnWidth = 6
> End If
> Next
> End Sub
>
> Regards,
> Per
>
> "primed" <primed(a)discussions.microsoft.com> skrev i meddelelsen
> news:FAB13FFF-37FD-44C7-9D3F-3111DAD7D65A(a)microsoft.com...
> > Hi,
> >
> > Can anyone help with code to do the following:
> >
> > Row E8:AI8 - each cell contains a value 0 to 100, generally a value of 1
> > to
> > 20. The total of the row is maximum 100.
> > I would like to adjust the width of the column based on the values for
> > each
> > column.
> > The minimum width would need to be 6 so text is still readable.
> >
> > Thanks in advance
> >
> > Primed
>
> .
>