Report All Column Widths In Active Worksheet - Corrected
 

Thanks for all input! Here's the lastest version with 2 Dim corrections:
Dim col As Range
Dim ColWidth As Variant

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 col As Range
Dim ColWidth As Variant
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").HorizontalAl ignment = xlCenter
Application.StatusBar = False

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

End Sub

Report All Column Widths In Active Worksheet - Corrected
 

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?

--
Regards,
Tom Ogilvy


"DataFreakFromUtah" wrote in message
om...
Thanks for all input! Here's the lastest version with 2 Dim corrections:
Dim col As Range
Dim ColWidth As Variant

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 col As Range
Dim ColWidth As Variant
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").HorizontalAl ignment = xlCenter
Application.StatusBar = False

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

End Sub