Report All Column Widths In Active Worksheet - an example

[DataFreakFromUtah]

Hello,
No question here, just a procedure for the archive.

Search criteria: return column widths for all columns in active
worksheet get column widths get column width report column widths
examine list column width find all column widths all columns widths
Column's width columns' widths

Note: this procedure only works with Excel 2000 or higher as it uses
the Split function. Also this procedure rounds column widths to the
nearest whole integer. You may be able to get around this by editing
the procedure to use point widths and then do a conversion. I couldn't
find anything in the archive on how to do this, but then again, I
didn't look very hard either.

Sub ColumnWidthReport()

'Creates a new report worksheet that returns the column ID
'and each column's width. Only works with Excel 2000 or higher

Dim cell As Range
Dim ColumnWidthReportSheet As Worksheet
Dim TargetWorksheet As Worksheet
Dim ColWidth As Integer
Dim Row As Integer

On Error Resume Next

'Add a new worksheet
Application.ScreenUpdating = False
Set TargetWorksheet = ActiveWorkbook.ActiveSheet
Set ColumnWidthReportSheet = ActiveWorkbook.Worksheets.Add
ColumnWidthReportSheet.Name = "ColumnWidths in " &
TargetWorksheet.Name

ColWidth = 1

'Set up the column headings for Report worksheet
With ColumnWidthReportSheet
Range("A1") = "Column Number"
Range("B1") = "Column Letter"
Range("C1") = "Column Width"

Range("A1:C1").Font.Bold = True
End With

'Process each column
Row = 2
For Each col In TargetWorksheet.Columns
'Derive column width of the column
ColWidth = col.ColumnWidth

With ColumnWidthReportSheet
Cells(Row, 1).Value = col.Column
Cells(Row, 2).Value = Split(Columns(col.Column).Address,
"$")(2)
Cells(Row, 3).Value = ColWidth

Row = Row + 1

End With

Next

'Adjust column widths on Report sheet
ColumnWidthReportSheet.Columns("A:C").AutoFit
ColumnWidthReportSheet.Columns("A:C").HorizontalAlignment =
xlCenter
Application.StatusBar = False

'Select a cell on the top of the report worksheet
Range("A2").Select

End Sub

 

[Norman Jones]

Hi DataFreak,

Also this procedure rounds column widths to the
nearest whole integer. You may be able to get around this by editing
the procedure to use point widths and then do a conversion. I couldn't
find anything in the archive on how to do this, but then again, I
didn't look very hard either.

That is because you declared ColWidth As Integer!

Try changing the variable type.

 

[Tom Ogilvy]

With ColumnWidthReportSheet
Range("A1") = "Column Number"
Range("B1") = "Column Letter"
Range("C1") = "Column Width"

Range("A1:C1").Font.Bold = True
End With

Why use the With statement here since nothing references it?