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.
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.