Set Column Width - question about shg code February 2008

M1k3 · Mar 17th 2020

In Feb 2008, shg posted this code to help a forum user. It is close to what I want, but I cannot get the correct modification. I want to change the column width to a number that is found in cell A1 in another worksheet. I have tried [3. Columns("C").ColumnWidth=Sheet2!A1 without success.

Any suggestions?

Thanks. Mike

Code

Sub SetColumnWidth()
Columns("A:B").ColumnWidth = 3.14
Columns("C").ColumnWidth = 8
Columns("D").ColumnWidth = 13.57
Columns("E").ColumnWidth = 24.14
Columns("F").ColumnWidth = 9
Columns("G").ColumnWidth = 10
Columns("H").ColumnWidth = 11.29
Columns("I").ColumnWidth = 8.57
Columns("J").ColumnWidth = 6.86
Columns("K").ColumnWidth = 8
Columns("L").ColumnWidth = 13
Columns("M").ColumnWidth = 10
End Sub

Fluff13 · Mar 17th 2020

How about

Code

Columns("C").ColumnWidth = Sheets("Sheet2").range("A1").value

M1k3 · Mar 17th 2020

Many thanks, Fluff13. That works perfectly.

So simple I should have been able to figure it out myself. Thanks again.

**Jack in the UK** · Mar 17th 2020

A little bit fuller code, sorry Jack had to write this one up for you

jiuk

Code

Option Explicit

Sub myWIDTH()
'** . Columns("C").ColumnWidth=Sheet2!A1 without success.

Dim myColumn As Long
myColumn = 3 ' i.e. Column C

Dim myWidth_VALUE
myWidth_VALUE = Sheets("Sheet2").Range("A1").Value

With Columns("C")
    .ColumnWidth = myWidth_VALUE
    
End With

theEND:
' jiuk - do something
Exit Sub

End Sub

Display More

M1k3 · Mar 17th 2020

Thanks, Jack in UK.

Mike

**Jack in the UK** · Mar 17th 2020

No problem, I would use Fluff code it is a lot less work and easy enough to edit if required

jiuk

Fluff13 · Mar 17th 2020

Quote from Jack in the UK

I would use Fluff code it is a lot less work and easy enough to edit if required

Not to mention shorter

Quote from M1k3

Many thanks, Fluff13. That works perfectly.

You're welcome & thanks for the feedback

jolivanes · Mar 18th 2020

Re: "Not to mention shorter"

Columns(3).ColumnWidth = [Sheet2!A1]

Set Column Width - question about shg code February 2008

Participate now!

Share