XL: How to Copy Column Widths

ID: Q61269

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0, 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel for OS/2, versions 2.2 and 3.0
Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0, 4.0a, 5.0, 5.0a

SUMMARY

In Microsoft Excel, you can copy the column width when you copy data from one range of cells to another by selecting the entire column when you copy it. You can also copy column widths separately from your data.

In versions of Microsoft Excel earlier than version 3.0, there is no direct way to copy column widths. You can, however, create a macro to copy column widths along with cell data.

MORE INFORMATION

Copying Column Widths in Excel Versions 3.0 and later

To copy column widths from one range of cells to another, follow the appropriate procedure below.

Method 1: To copy column widths along with your data:

Select the columns you want to copy.

- To select a single column, select the column heading or move to any cell in the column and press CTRL+SPACEBAR (COMMAND+SPACEBAR if you are using a Macintosh computer).

- To select more than one column:

a. Select the first column.

b. Hold down the SHIFT key and use the arrow keys to select the remaining columns.

From the Edit menu, choose Copy.

Select any cell in your destination range (the range where you want to paste your data).

From the Edit menu, choose Paste.

When you choose the Paste command, Microsoft Excel will paste the column widths along with your data.

Method 2: To copy the column widths separately from your data:

NOTE: This method copies all of the formats, not just the column widths, from the selected columns to the destination range.

Copy your data to the new area by selecting only the cells that you want to copy and use the Copy and Paste commands.

Select the columns in your source range (the range you are copying from).

- To select a single column, select the column heading or move to any cell in the column and press CTRL+SPACEBAR (COMMAND+SPACEBAR for the Macintosh).

- To select more than one column:

a. Select the first column.

b. Hold down the SHIFT key and use the arrow keys to select your remaining columns.

Move to the first cell in your destination area (the upper- left cell of the area where you pasted your data).

From the Edit menu, choose Paste Special.

In the Paste Special dialog box, select the Formats option.

Choose the OK button. When you choose the OK button, Microsoft Excel pastes the column formats over the columns in your destination area.

Macro for Copying Column Widths in Excel Version 2.x

The following macro copies the data from a specified source range to a specified destination range and applies the column width from the first column in the source range to all the columns of the destination range.

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

Sample Macro


A1: Column_Copy
A2: =GET.CELL(16,ACTIVE.CELL())
A3: =SELECT(OFFSET(ACTIVE.CELL(),0,0,<# Rows>,<# Cols>))
A4: =COPY()
A5: =SELECT(OFFSET(!<DestCell>,0,0,<# Rows>,<# Cols>))
A6: =PASTE()
A7: =COLUMN.WIDTH(A2)
A8: =CANCEL.COPY()
A9: =RETURN()

NOTE: To use this macro you must first select the upper-left cell in your source range. This cell is the active cell, that is, the currently selected cell. The following is an explanation of the variables used in the above macro:


   <DestCell> - The cell in the upper left corner of your destination
                range.

   <# Rows> - The number of rows you want to copy.

   <# Cols> - The number of columns you want to copy.

Line-by-Line Description of the Macro

A1: Column_Copy is the name of the macro.

A2: The GET.CELL() function returns the column width of the active cell.

A3: Select the source range where:


       <# Rows> is the number of rows you want to copy.
       <# Cols> is the number of columns you want to copy.

A4: Copy the selected range.

A5: Select the destination range, where:


    <DestCell> is the cell in the upper left corner of your
               destination range.
    <# Rows> is the number of rows you want to copy.
    <# Cols> is the number of columns you want to copy.

A6: Paste the copied data in the destination range.

A7: Apply the column width from the first column in the source range to all columns in the destination range.

A8: Turn off the moving border around the source range.

A9: End of the macro.

REFERENCES

"Microsoft Excel User's Guide 2," version 4.0, pages 231-255

For more information about creating macros, see pages 231-255 of the version 4.0 "Microsoft Excel User's Guide 2."

Additional query words: howto 2.0 2.00 2.01 2.1 2.10 2.20 2.21 3.0 XL97 XL7 XL5 XL4 XL3 XL2

Keywords :
Version :
Platform :
Issue type :