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.

 

[Rick Rothstein]

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

Just nothing that your macro can also be written without using a loop...

Sub rudevincy()
  With Range("E2", Cells(2, Columns.Count).End(xlToLeft))
    .Columns.ColumnWidth = Evaluate("{" & Join(Application.Index(.Value, 1, 0), ",") & "}")
  End With
End Sub

 

[rudevincy]

In d6 is the dropdown to select the team, E6 - AB6 is the range of where the Names will be displayed
so in E2 the formula asks if there is a name in E6.. etc then value will be 9 if no name then value is 0
=IF(E6="",0,9)

and based off that value 0 or 9 that will set the column width

 

[Rick Rothstein]

What is the formula in E2?

I think the problem is that if some of the ending columns were hidden by past action, then this will not see values in the hidden columns in order to reset the ending column...

Cells(2, Columns.Count).End(xlToLeft).Column

this will...

Rows(2).Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column

So try changing your (Fluff's code) code to this...

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 Rows(2).Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
         Columns(i).ColumnWidth = Cells(2, i).Value
      Next i
   End If
End Sub

Edit Note: Posted Fluff's latest code (originally I used his older code) with the above recommended change.

 

[Fluff]

Another option

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
      Columns("E:XFD").ColumnWidth = 10
      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]

I am stepping through this code and I notice that when I select from the dropdown ( the code runs before the formulas calculate) and because of that its not adjusting the proper columns, is there a way to after making the selection from the dropdown list can the formulas calculate and then adjust the columns

 

[Rick Rothstein]

Where is this drop down located... cell D6 or on Row 2 between Column E and the last data column?

 

[rudevincy]

d6

 

[Fluff]

Is calculation set to automatic or manual?

 

[rudevincy]

it is set to automatic

I changed the code a bit and it is working well, this is what I have now

Private Sub Worksheet_Calculate()
Dim i As Long
Dim Xrg As Range

Set Xrg = Sheets("Top 5 Report - Sales").Range("d6")
If Not Intersect(Xrg, Range("d6")) Is Nothing Then

Columns("E:AD").ColumnWidth = 9

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

thank you

 

[Fluff]

Glad you sorted it & you can simplify that like

Private Sub Worksheet_Calculate()
   Dim i As Long
   Columns("E:AD").ColumnWidth = 9
  
   For i = 5 To Cells(2, Columns.Count).End(xlToLeft).Column
      Columns(i).ColumnWidth = Cells(2, i).Value
   Next i
End Sub