Column Width Adjust

[rudevincy]

Hello,

I have a worksheet that is populated with data in some columns.

in MyRange is E2:BA2 is a formula that calculates the number of characters in another cell that is based on the column width... so if in that cell(E2) the value is 5 then the column width should be adjusted to 5, but if the value in the cell is 0 then the column width should be 0.

Please I need help with the formula as it is not working right

Sub SizeColumns()

Application.ScreenUpdating = False
Sheets("SheetData").Activate

Set MyRange = Worksheets("SheetData").Range("E2:AB2")
countNonBlank = Application.WorksheetFunction.CountA(MyRange)

zLastCol = countNonBlank

For MyCol = 1 To zLastCol

If Cells(MyCol, 2).Value = "5" Then
Cells(MyCol, 2).ColumnWidth = "5"

Else
Cells(MyCol, 2).ColumnWidth = "0"

End If

Next MyCol

End Sub

Your help is greatly appreciated.

 

[Fluff]

How about

Sub rudevincy()
   Dim i As Long
   
   For i = 5 To Cells(2, Columns.Count).End(xlToLeft).Column
      Columns(i).ColumnWidth = Cells(2, i).Value
   Next i
End Sub

 

[rudevincy]

thank you for your reply but that is not want I need, but my range is E2:BA2 changes data based off the data that is select
So when Team 1 is select it may have 3 columns that have data so for example in cell E2, C2, AB2, and show data so I would need their columns to be set to 5 but because the other cell have no data I want the column to be set to 0 so it is not seen.

then if Team 3 is selected they have 10 columns of data and the columns width are to be set and those that show no data should be set to 0

 

[Fluff]

In what way doesn't it work?

 

[rudevincy]

sorry it worked I had some code in there that is was conflicting with ... thank you sorry for the confusion

 

[Fluff]

You're welcome & thanks for the feedback.

 

[rudevincy]

one last thing the code works good on its own but when I add to the rest of the code it is not working, it is not changing the column width.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long

Set Target = Intersect(Target, Range("d6"))
If Target Is Nothing Then

For i = 5 To Cells(2, Columns.Count).End(xlToLeft).Column
Columns(i).ColumnWidth = Cells(2, i).Value
Next i

End if
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim i As Long
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "D6" Then
      For i = 5 To Cells(2, Columns.Count).End(xlToLeft).Column
         Columns(i).ColumnWidth = Cells(2, i).Value
      Next i
   End If
End Sub

 

[rudevincy]

this code almost works so when I select Team 1 in d6 there are 10 columns of data displayed, then I select Team 3 from d6 that has 6 columns of data displayed, but when I select Team 2 which has 12 columns of data it is only showing 6 columns it does not adjust the column width to show all, if I reselect Team 1 it is still just showing the 6 columns of data,

is there a way to when the value is change it resets the columns to a default width of 10 and then set the columns with data to 9 and those without to 0 (in E2.. etc. is the value that I want the column to set to but even though there is a value it is not resetting the column width.

 

[Fluff]

What is the formula in E2?