Display all column width in Excel

[ghans_12]

Hi, I have lots of column in a excel sheet and I need to know the width of each of the column.
Is there any way, that all the width of columns will be displayed to me at one time rather than me going and checking them one-by-one.

Thanks

 

[MrKowz]

Insert a row at the top of a copy of your sheet and run the following code:

Public Sub ColWidth()
Dim i As Long
For i = 1 To 256
   Cells(1, i).Value = Columns(i).ColumnWidth
Next i
End Sub

 

[dave3009]

Perhaps a macro to test the size of your data, then insert a row and enter the column width

Sub InsertColumnWidths()
Dim LstCol As Long
Dim my As Range
Dim c As Range
LstCol = Cells(1, Columns.Count).End(xlToLeft).Column
Range("1:1").Insert xlDown
Set my = Cells(1, 1).Resize(, LstCol)
For Each c In my
    c.Value = c.Width
Next c
End Sub

 

[njimack]

Something like this perhaps? Every time you select a different cell, it will place the column width in every cell of row 1.

However, the numbers it returns are very different from right-clicking a column and viewing the width.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range

For Each cell In Range(Cells(1, 1), Cells(1, Columns.Count))
    cell.Value = cell.Width
Next cell
End Sub

 

[MickG]

Try :-

[COLOR="Navy"]Sub[/COLOR] MG28Oct05
[COLOR="Navy"]Dim[/COLOR] Col [COLOR="Navy"]As[/COLOR] Range, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Col [COLOR="Navy"]In[/COLOR] ActiveSheet.UsedRange.Columns
    Txt = Txt & Col.Column & ";-  " & Col.Width & Chr(10)
[COLOR="Navy"]Next[/COLOR] Col
MsgBox "Col Num." & "Col Width" & Chr(10) & Txt
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[JackDanIce]

Try:

Sub ColumnWidth()
Dim i As Long
i = 1
Range("1:1").Insert shift:=xlDown
Do
    Cells(1, i).Value = Cells(2, i).Width
    i = i + 1
Loop Until IsEmpty(Cells(2, i))
End Sub

 

[ghans_12]

Thanks everyone for your help.