jdellasala
Well-known Member
- Joined
- Dec 11, 2020
- Messages
- 751
- Office Version
- 365
- Platform
- Windows
- Mobile
- Web
CELLWIDTH returns the cell width of the optionally provided Cell Reference.
It returns ONLY the cell width, not the Spilled Array CELL("width") now returns. Note that the value returned is the width of the column "rounded off to an integer".
Used on its own, CELL("width") returns a one row, two column array of the width of the column referenced in the left cell, and "TRUE if the column width is the default or FALSE if the width has been explicitly set by the user" in the right cell. That TRUE/FALSE "default" is based on the value of the Template used, not Excel's default width. My Excel setup starts up with and uses a Template where the column width for Sheet1 is 10 (and row height is 15). I learned here that [Ctrl]N will also use that template for a New Workbook, however, if I use File -> New -> Blank Workbook, Excel's true defaults are used - Column Width of 8.54 (reported as 9), and a row height of 14.6.
Probably worth noting here that neither CELL nor CELLWIDTH work with Arrays. (perhaps another LAMBDA CELLWIDTHS()?
Back in 2020 (2019?) I had a large Worksheet where I had a LOT of columns to adjust the width of. Other things also had to be done, so I wrote a VBA script to automate all the changes. One of the things the VBA Script did was to add the formula CELL("width") above the column headers so it could be used after rearranging columns. Then suddenly one day the script broke badly because the formula was in a lot of adjacent cell and returned #SPILL! instead of the value needed.
The LAMBDA is VERY simplistic, but after browsing this board a lot, it doesn't appear that there's anything too short and basic to post as a LAMBDA!
Maybe this is something you'll need one day!
Disclaimer: I use a leading underscore for my LAMBDAs to make them easier to access when a lot are loaded. You're obviously welcome to call it whatever you want!
Excel Formula:
=LAMBDA([Cell],IF(ISOMITTED(Cell),INDEX(CELL("width"),1),INDEX(CELL("width",Cell),1)))Used on its own, CELL("width") returns a one row, two column array of the width of the column referenced in the left cell, and "TRUE if the column width is the default or FALSE if the width has been explicitly set by the user" in the right cell. That TRUE/FALSE "default" is based on the value of the Template used, not Excel's default width. My Excel setup starts up with and uses a Template where the column width for Sheet1 is 10 (and row height is 15). I learned here that [Ctrl]N will also use that template for a New Workbook, however, if I use File -> New -> Blank Workbook, Excel's true defaults are used - Column Width of 8.54 (reported as 9), and a row height of 14.6.
Probably worth noting here that neither CELL nor CELLWIDTH work with Arrays. (perhaps another LAMBDA CELLWIDTHS()?
Back in 2020 (2019?) I had a large Worksheet where I had a LOT of columns to adjust the width of. Other things also had to be done, so I wrote a VBA script to automate all the changes. One of the things the VBA Script did was to add the formula CELL("width") above the column headers so it could be used after rearranging columns. Then suddenly one day the script broke badly because the formula was in a lot of adjacent cell and returned #SPILL! instead of the value needed.
The LAMBDA is VERY simplistic, but after browsing this board a lot, it doesn't appear that there's anything too short and basic to post as a LAMBDA!
Maybe this is something you'll need one day!
Disclaimer: I use a leading underscore for my LAMBDAs to make them easier to access when a lot are loaded. You're obviously welcome to call it whatever you want!
Upvote
0
