Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Column Widths Different For Different Users | Excel Discussion (Misc queries) | |||
Different column widths within one worksheet | Excel Discussion (Misc queries) | |||
Are different column widths possible on the same worksheet? | Excel Worksheet Functions | |||
Can I have different column widths in a worksheet? | Excel Worksheet Functions | |||
Report All Column Widths In Active Worksheet - an example | Excel Programming |