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]

@Fluff,

Your code (and the OP's) will run for every recalculation of the sheet whether cell D6 triggered the recalculation or not. That means the columns are continuously being reset even when not needed.

 

[Fluff]

Yes I know.
The OP seemed happy with that code, so I simply removed the redundant portion.

 

[rudevincy]

thank you for your help and in such a timely fashion. Greatly appreciated