MACRO TO ADJUST COLOUMN WIDTH BY VALUE

[K]

Hi, Please if anybody tell me the macro that if I put any thing in
any cell of any coloumn
of whole sheet then coloumn should get adjusted by that value width.
Thanks

 

[michael.beckinsale]

Hi,  Please if anybody tell me the macro that if I put any thing in
any cell of any coloumn
of whole sheet then coloumn should get adjusted by that value width.
Thanks

Hi K,

Paste this code into the sheet code module (copy code, right click
sheet name tab, click view code, paste code)

**Beware word wrapping**

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ActiveSheet.Cells) Then
ActiveCell.ColumnWidth = Target.Value
End If
End Sub

Regards

Michael

 

[K]

Hi K,

Paste this code into the sheet code module (copy code, right click
sheet name tab, click view code, paste code)

**Beware word wrapping**

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ActiveSheet.Cells) Then
    ActiveCell.ColumnWidth = Target.Value
End If
End Sub

Regards

Michael

Hi michael, thanks for replying. i used your macro but its giving
"Run time error 13" and highlighting this line
If Intersect(Target, ActiveSheet.Cells) Then
is there any sultion for this please

 

[michael.beckinsale]

Hi K,

Are you sure you have pasted the code into the correct place as per my
email/posting ?

I have tested this and it works perfectly on my set-up.

 

[K]

Hi K,

Are you sure you have pasted the code into the correct place as per my
email/posting ?

I have tested this and it works perfectly on my set-up.

yes i am sure i did exactely the way you told me and its giving error
message. does this make difference as i am using excel 2007

 

[michael.beckinsale]

Hi K,

I think l have identified the problem by re-reading your original
post. If my interpretation is correct you want to autofit the column
to an entry in any cell, whether you enter text or numbers. My
oroginal solution was for numbers only. The code posted below will set
the column to the width od the widest entry.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyCol As Integer
MyCol = Intersect(Target, ActiveSheet.Cells).Column
Columns(MyCol).AutoFit

End Sub

This is tried & tested in XL2007

Regards

Michael



I

 

[K]

Hi K,

I think l have identified the problem by re-reading your original
post. If my interpretation is correct you want to autofit the column
to an entry in any cell, whether you enter text or numbers. My
oroginal solution was for numbers only. The code posted below will set
the column to the width od the widest entry.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyCol As Integer
       MyCol = Intersect(Target, ActiveSheet.Cells).Column
       Columns(MyCol).AutoFit

End Sub

This is tried & tested in XL2007

Regards

Michael

I

Thanks Michael. this the one i wanted.