Hi all i am not a programmer but a dabbler and need help with changing the
widths of 51 columns, all variable in width.
What would be the best way to do this, it must be done by code ?
A lot of the columns are the same width, but spaced apart from each other..

Any help would be greatly appreciated
--
Les

"Les" <(E-Mail Removed)> wrote in message
news:3C163B05-F8D1-4800-B6ED-(E-Mail Removed)...
> Hi all i am not a programmer but a dabbler and need help with changing the
> widths of 51 columns, all variable in width.
> What would be the best way to do this, it must be done by code ?
> A lot of the columns are the same width, but spaced apart from each
> other..
>
> Any help would be greatly appreciated
> --
> Les

The easiest way to do this is to go to the developer tab, turn on record
macro, change the width of a couple of columns, select some columns then
change their with, stop macro recording and press alt-F11 to look at what
was recorded.

In this case you get the code below from which you should be able to work
out how to do whatever it is you want to do with these articular column
widths:

Sub Macro1()
'
' Macro1 Macro
'
Columns("D").ColumnWidth = 12.86
Columns("G:G").ColumnWidth = 14.43
Columns("J:M").Select
Selection.ColumnWidth = 10.43
End Sub

Hi Lorne,

I do know that method, thanks i just thought there might be an easer way
using an array... ?
--
Les


"Lorne" wrote:

> "Les" <(E-Mail Removed)> wrote in message
> news:3C163B05-F8D1-4800-B6ED-(E-Mail Removed)...
> > Hi all i am not a programmer but a dabbler and need help with changing the
> > widths of 51 columns, all variable in width.
> > What would be the best way to do this, it must be done by code ?
> > A lot of the columns are the same width, but spaced apart from each
> > other..
> >
> > Any help would be greatly appreciated
> > --
> > Les
>
> The easiest way to do this is to go to the developer tab, turn on record
> macro, change the width of a couple of columns, select some columns then
> change their with, stop macro recording and press alt-F11 to look at what
> was recorded.
>
> In this case you get the code below from which you should be able to work
> out how to do whatever it is you want to do with these articular column
> widths:
>
> Sub Macro1()
> '
> ' Macro1 Macro
> '
> Columns("D").ColumnWidth = 12.86
> Columns("G:G").ColumnWidth = 14.43
> Columns("J:M").Select
> Selection.ColumnWidth = 10.43
> End Sub
>
>
>
>

Range("A:H,K:P").Select
Selection.EntireColumn.AutoFit
--
Mike Q.


"Les" wrote:

> Hi Lorne,
>
> I do know that method, thanks i just thought there might be an easer way
> using an array... ?
> --
> Les
>
>
> "Lorne" wrote:
>
> > "Les" <(E-Mail Removed)> wrote in message
> > news:3C163B05-F8D1-4800-B6ED-(E-Mail Removed)...
> > > Hi all i am not a programmer but a dabbler and need help with changing the
> > > widths of 51 columns, all variable in width.
> > > What would be the best way to do this, it must be done by code ?
> > > A lot of the columns are the same width, but spaced apart from each
> > > other..
> > >
> > > Any help would be greatly appreciated
> > > --
> > > Les
> >
> > The easiest way to do this is to go to the developer tab, turn on record
> > macro, change the width of a couple of columns, select some columns then
> > change their with, stop macro recording and press alt-F11 to look at what
> > was recorded.
> >
> > In this case you get the code below from which you should be able to work
> > out how to do whatever it is you want to do with these articular column
> > widths:
> >
> > Sub Macro1()
> > '
> > ' Macro1 Macro
> > '
> > Columns("D").ColumnWidth = 12.86
> > Columns("G:G").ColumnWidth = 14.43
> > Columns("J:M").Select
> > Selection.ColumnWidth = 10.43
> > End Sub
> >
> >
> >
> >

don't know which columns, if they're consecutive or not, but you could possibly
use an array to store the widths.

--


Gary


"Les" <(E-Mail Removed)> wrote in message
news:3C163B05-F8D1-4800-B6ED-(E-Mail Removed)...
> Hi all i am not a programmer but a dabbler and need help with changing the
> widths of 51 columns, all variable in width.
> What would be the best way to do this, it must be done by code ?
> A lot of the columns are the same width, but spaced apart from each other..
>
> Any help would be greatly appreciated
> --
> Les

Hi Gary, thanks for the reply that is what i had in mind. Could you help me
by showing me how one would do that Please.

Thanks in advance,
--
Les


"Gary Keramidas" wrote:

> don't know which columns, if they're consecutive or not, but you could possibly
> use an array to store the widths.
>
> --
>
>
> Gary
>
>
> "Les" <(E-Mail Removed)> wrote in message
> news:3C163B05-F8D1-4800-B6ED-(E-Mail Removed)...
> > Hi all i am not a programmer but a dabbler and need help with changing the
> > widths of 51 columns, all variable in width.
> > What would be the best way to do this, it must be done by code ?
> > A lot of the columns are the same width, but spaced apart from each other..
> >
> > Any help would be greatly appreciated
> > --
> > Les
>
>
>

maybe something like this. this would set the width for the first 10 columns.
the option base 1 set the 1st element position to 1 instead of 0.

if you only had option explicit at the top, you have to use this line instead:
ws.Columns(i).ColumnWidth = arr(i - 1)



Option Base 1
Sub new_column_width()
Dim i As Long
Dim arr As Variant
Dim ws As Worksheet

arr = Array("25", "12", "8", "13", "9", "9", "12", "10", "12", "20")
Set ws = Worksheets("Sheet1")

For i = 1 To 10
ws.Columns(i).ColumnWidth = arr(i)
Next
End Sub


--


Gary


"Les" <(E-Mail Removed)> wrote in message
news:BDA85809-29F3-4D55-8249-(E-Mail Removed)...
> Hi Gary, thanks for the reply that is what i had in mind. Could you help me
> by showing me how one would do that Please.
>
> Thanks in advance,
> --
> Les
>
>
> "Gary Keramidas" wrote:
>
>> don't know which columns, if they're consecutive or not, but you could
>> possibly
>> use an array to store the widths.
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Les" <(E-Mail Removed)> wrote in message
>> news:3C163B05-F8D1-4800-B6ED-(E-Mail Removed)...
>> > Hi all i am not a programmer but a dabbler and need help with changing the
>> > widths of 51 columns, all variable in width.
>> > What would be the best way to do this, it must be done by code ?
>> > A lot of the columns are the same width, but spaced apart from each other..
>> >
>> > Any help would be greatly appreciated
>> > --
>> > Les
>>
>>
>>

Thank you so much Gary.
In your opinion is this the best way to handle this ?
--
Les


"Gary Keramidas" wrote:

> maybe something like this. this would set the width for the first 10 columns.
> the option base 1 set the 1st element position to 1 instead of 0.
>
> if you only had option explicit at the top, you have to use this line instead:
> ws.Columns(i).ColumnWidth = arr(i - 1)
>
>
>
> Option Base 1
> Sub new_column_width()
> Dim i As Long
> Dim arr As Variant
> Dim ws As Worksheet
>
> arr = Array("25", "12", "8", "13", "9", "9", "12", "10", "12", "20")
> Set ws = Worksheets("Sheet1")
>
> For i = 1 To 10
> ws.Columns(i).ColumnWidth = arr(i)
> Next
> End Sub
>
>
> --
>
>
> Gary
>
>
> "Les" <(E-Mail Removed)> wrote in message
> news:BDA85809-29F3-4D55-8249-(E-Mail Removed)...
> > Hi Gary, thanks for the reply that is what i had in mind. Could you help me
> > by showing me how one would do that Please.
> >
> > Thanks in advance,
> > --
> > Les
> >
> >
> > "Gary Keramidas" wrote:
> >
> >> don't know which columns, if they're consecutive or not, but you could
> >> possibly
> >> use an array to store the widths.
> >>
> >> --
> >>
> >>
> >> Gary
> >>
> >>
> >> "Les" <(E-Mail Removed)> wrote in message
> >> news:3C163B05-F8D1-4800-B6ED-(E-Mail Removed)...
> >> > Hi all i am not a programmer but a dabbler and need help with changing the
> >> > widths of 51 columns, all variable in width.
> >> > What would be the best way to do this, it must be done by code ?
> >> > A lot of the columns are the same width, but spaced apart from each other..
> >> >
> >> > Any help would be greatly appreciated
> >> > --
> >> > Les
> >>
> >>
> >>
>
>
>

i'm not qualified to answer that one. i know i've used this method before. maybe
someone who knows more than me will have an opinion.

--


Gary


"Les" <(E-Mail Removed)> wrote in message
news:0AB3DDBA-562D-4FB9-BC8C-(E-Mail Removed)...
> Thank you so much Gary.
> In your opinion is this the best way to handle this ?
> --
> Les
>
>
> "Gary Keramidas" wrote:
>
>> maybe something like this. this would set the width for the first 10 columns.
>> the option base 1 set the 1st element position to 1 instead of 0.
>>
>> if you only had option explicit at the top, you have to use this line
>> instead:
>> ws.Columns(i).ColumnWidth = arr(i - 1)
>>
>>
>>
>> Option Base 1
>> Sub new_column_width()
>> Dim i As Long
>> Dim arr As Variant
>> Dim ws As Worksheet
>>
>> arr = Array("25", "12", "8", "13", "9", "9", "12", "10", "12", "20")
>> Set ws = Worksheets("Sheet1")
>>
>> For i = 1 To 10
>> ws.Columns(i).ColumnWidth = arr(i)
>> Next
>> End Sub
>>
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Les" <(E-Mail Removed)> wrote in message
>> news:BDA85809-29F3-4D55-8249-(E-Mail Removed)...
>> > Hi Gary, thanks for the reply that is what i had in mind. Could you help me
>> > by showing me how one would do that Please.
>> >
>> > Thanks in advance,
>> > --
>> > Les
>> >
>> >
>> > "Gary Keramidas" wrote:
>> >
>> >> don't know which columns, if they're consecutive or not, but you could
>> >> possibly
>> >> use an array to store the widths.
>> >>
>> >> --
>> >>
>> >>
>> >> Gary
>> >>
>> >>
>> >> "Les" <(E-Mail Removed)> wrote in message
>> >> news:3C163B05-F8D1-4800-B6ED-(E-Mail Removed)...
>> >> > Hi all i am not a programmer but a dabbler and need help with changing
>> >> > the
>> >> > widths of 51 columns, all variable in width.
>> >> > What would be the best way to do this, it must be done by code ?
>> >> > A lot of the columns are the same width, but spaced apart from each
>> >> > other..
>> >> >
>> >> > Any help would be greatly appreciated
>> >> > --
>> >> > Les
>> >>
>> >>
>> >>
>>
>>
>>

Hi Les

you are talking about "the widhts are variable" but then you try to
use fixed widhts which are stored in an array.
Just to clarify the situation: Do those 51 Lines all have fixed widths
but all different widths, or do those widths vary depending on the
data?

in case 1)
you can use Garys answer

in case 2)
you should try the Autofit approach of Mike

Any other questions, just ask.

hth

Carlo