Copying and Pasting Column Widths in Microsoft Excel
Spreadsheet users are sometimes frustrated by perceived inconsistencies in Excel, for instance, sometimes column widths paste with your data, but sometimes they don’t – leaving you to manually adjust the column widths.
In this article I’ll explain some of the nuance involved with copying and pasting column widths, as well as empower you to consistently paste column widths when you want to. I’ll also explain the nuances and limitations of pasting row heights, as well as a line of programming code you can use to transfer row heights from one worksheet to another.
With regard to the question of “will it paste my column widths or won’t it”, the answer depends upon what area of the spreadsheet you selected. When you copy a range of cells, such as shown in Figure 1, Excel does what you asked and pastes just the cell contents. However, as shown in Figure 2, when you copy entire columns and paste elsewhere, Excel then pastes the column widths as well.
Figure 1: Column widths do not automatically paste when you copy a range of cells.
Figure 2: You must copy entire columns in order to paste the column widths elsewhere.
Now that you know when Excel will transfer column widths, what about situations where you want to copy a range of cells, but still paste the column widths? In Excel 2010 and later you can use the Keep Source Column Widths option when pasting, as shown in Figure 3. Alternatively, in any version of Excel you can copy data from a range of cells, and then use the Paste Special command. Within the Paste Special dialog box you can double-click Column Widths.
Figure 3: The Keep Source Column Widths paste option enables you to apply column widths from a range of cells.
Conversely, you may need to copy entire columns, but opt not to paste the column widths. In such situations choose either FormulasandNumberFormats or Values and Number Formats, as shown in Figure 4.
Figure 4: The Formulas/Values and Number Formats options allow you to paste entire columns without transferring the widths.
Now that you have the lay of the land on pasting column widths, let’s see how to transfer row heights. You can transfer row heights from one section of a worksheet to another if you select entire rows.
To begin, manually adjust the row heights for two or more rows on a given worksheet. One way to do so is to right-click on a selection of rows and then choose Row Height. If you want to double the size of a standard row in a worksheet, enter 30 when prompted. Transferring row heights works the same was as Figure 1 and 2 illustrate for columns: row heights don’t transfer when you select a range of cells, only when you select entire rows.
Excel does not offer a Paste Special option for row heights, but you can use a line of programming code to transfer the row heights from one worksheet to the next worksheet in the series as illustrated in Figure 5.
- Press Alt-F11 on your keyboard to display Excel’s Visual Basic Editor. Mac users should press Fn-Alt-F11. Although it looks like a separate program, it’s a hidden aspect of Excel that most users haven’t seen before.
- Click the View menu within the Visual Basic Editor.
- Select Immediate Window, or press Ctrl-G on your keyboard (for Mac, Ctrl-Cmd-G).
- At this point the Immediate window will appear onscreen. This is a special area where any programming code you type will be executed immediately; hence the name.
- Type the following line of programming code into the Immediate window, exactly as written below, and then press Enter.
For each r in Selection.Rows: Worksheets(ActiveSheet.Index+1).Rows(r.Row).Rowheight=r.RowHeight: Next
The line of programming code must appear as a single line within the Immediate window.
The downside of the Immediate Window is you don’t get any direct feedback if your programming code worked, other than seeing that the row heights have changed on the worksheet after the one where you selected the rows. Error prompts will appear if you press Enter when the line of code is either incomplete or contains typographical errors.
You may also encounter an error if the worksheet referenced is protected by way of the Protect Sheet command on Excel’s Review menu or if you selected rows on the last sheet within a workbook.
- You can safely exit the Visual Basic Editor once you’ve run the line of code.
Figure 5: You can use a line of programming code to transfer row heights from one worksheet to another.
The aforementioned line of code utilizes Visual Basic for Applications in Microsoft Excel. This is known as an object-oriented programming language, so if you want a little insight as to what the macro is doing:
- For each r in Selection.Rows sets up a loop, meaning Excel will loop through each row that you’ve selected.
- Worksheets(ActiveSheet.Index+1) determines the position of the worksheet to be affected. ActiveSheet represents the current worksheet, while Index returns it’s position number within the workbook. Adding 1 to this index value instructs Excel to act on the next worksheet after the current worksheet.
- Rows(r.Row) is the row number to be affected.
- RowHeight =r.RowHeight transfers the row height from the worksheet where you made the selection to the row being affected.
- Next instructs Excel to start the process over again.
If you were to store this within a formal macro, the code might take this form:
For each r in Selection.Rows
The Immediate Window only allows us to execute a single line of code at a time, so the colons allow us to string three lines of code together into a single line that can be executed.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.