Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Determine a cell's width and enter it in the cell
I have several workbooks with multiple sheets that I'd like to have the same
various column widths on each sheet. I get it all set up and then one column will need to be wider. Now I have to go to a cell in each column, check column width, and enter it in each cell. I then have a sub that reads the value in a cell and sets the width to that value so that I can copy the row to each other sheet to resize them. Is there a way to automatically read a cell's width and enter that value in the cell? Thanks for any suggestions. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Determine a cell's width and enter it in the cell
As an alternative: you could "group" all the sheets in a workbook by
pressing CTRL and left clicking each tab. When sheets are grouped any change made to one sheet will be made on all the grouped sheets. Then autosize each column- all columns will now be a readable width. Ungroup your sheets afterwards by right-clicking on a grouped sheet and selecting Ungroup Sheets. You could even automate this process by recording it as a macro. Since you're using code you can get a column's width with this code: dim ColWidth as double ColWidth = ActiveCell.Width |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Determine a cell's width and enter it in the cell
Thanks much for the help.
The code I wrote to set column widths is: Dim ColWid$ Do Until ActiveCell.Value = "" ColWid$ = ActiveCell.Value Selection.ColumnWidth = ColWid$ ActiveCell.Offset(0, 1).Range("A1").Select Loop To use it, I manually check the column width for each column of a sheet I want to duplicate and enter it manually in a row of each column. Then copy that row of all the column widths to the new sheet. Run the code and my columns are set. I would love to have a little piece of code that reads the column width and enters it in the active cell. When I tried ActiveCell.Value = ActiveCell.Width, it returned 48 in the cell instead of 8.43 I got when checking the column width manually. Thought it might be pixels or something, but different width columns didn't return consistent ratios, like 98.25 on a column 18 wide. Any help would be greatly appreciated. "Dave O" wrote: As an alternative: you could "group" all the sheets in a workbook by pressing CTRL and left clicking each tab. When sheets are grouped any change made to one sheet will be made on all the grouped sheets. Then autosize each column- all columns will now be a readable width. Ungroup your sheets afterwards by right-clicking on a grouped sheet and selecting Ungroup Sheets. You could even automate this process by recording it as a macro. Since you're using code you can get a column's width with this code: dim ColWidth as double ColWidth = ActiveCell.Width |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pricing problems | Excel Worksheet Functions | |||
Text disappears when word wrap is used | Excel Discussion (Misc queries) |