Excel Yourself: Getting across the line

Excel Yourself

A simple double-click of the mouse lets Excel populate thousands of rows downwards, but copying across a column is a whole different issue.

Question

Is there a shortcut to copy across columns in the same way that double clicking the Fill Handle copies down?

Answer

The Fill Handle is the mouse cursor that is a small black cross at the bottom, right-hand corner of a selected cell, or range. When you double-click the Fill Handle, Excel will copy down if there are entries below, to the left or to the right. It is a great time-saving shortcut. It can copy down thousands of rows without you selecting anything. It stops when it encounters a blank row.

Unfortunately there is no such shortcut to copy to the right, across columns. Ctrl + R copies to the right, but you have to select the range first, so you might as well just drag the Fill Handle to the right to copy.

Download a sample copy across spreadsheet

Because copying across is such a common practice when developing spreadsheets, I wrote a macro many years ago to copy across in one click. Macros are useful for frequent processes.

Figure 1

Figure 1 has the macro that copies the selected range across columns as long as there are entries in either of the two rows above.

You need to select a single column range before running the macro.

The blue circled numbers on the left are used to identify parts of the macro that are covered in the explanation that follows.

This is not a recorded macro. It was written from scratch and took about 20 minutes to write and test. It has saved me hours of work over the years when developing financial, reporting and budget models.

This macro will not overwrite any cells. In the companion Excel file I have included another version that will overwrite cells. You would use the overwrite version if you had already copied across and you need to amend the formula. This version is typically used when creating a sheet.

Explaining the macro

  1. This macro has error handling included. If an error is encountered the macro will go directly to the line near the bottom labelled HandleError:, see note 11. The Merged Cells format is a common reason why copy macros can fail.
  2. These two lines declare two variables to be used later in the code.
  3. These two commands are commonly used to speed up macros. The first command stops the screen from flickering and the second turns off calculation.
  4. The macro is designed to work on a single range that is one column wide. This If statement confirms these two restrictions. If the range has more than one column or more than one range is selected, the macro will go to the HandleExit: label, see note 10. (In Excel you can select more than one range at a time by holding down the Ctrl key while selecting ranges.)
  5. This uses a For Each loop, a programming structure that will individually process every cell (the c variable) in the selected range. When the macro gets to the Next c line (between numbers 9 and 10 on Figure 1) it will loop back to this line and start again, until all the cells in the range have been processed.
  6. This is a For Next loop, a programming structure that allows you to perform commands a certain number of times. In this case we are setting the variable iCol to the numbers 1 to 100. These will be used to look at each of the 100 columns to the right of the selected range. Changing 100 to another number will change how many columns are handled. When the code gets to the Next iCol line it comes back to this line and starts again until it gets to 101.
  7. The c variable represents the current cell being reviewed. The If statement looks at the two rows above the current cell. The Offset command is used to look at the rows above (-1 and -2 are used) and the column to the right (iCol is used). The If statement also checks to ensure that the cell on the same row is blank. If either of the rows above have an entry and the cell on the same row is blank, then note 8 will be performed. If not, note 9 will be performed.
  8. This command actually performs the copy and paste process. It copies the selected cell and pastes it on the same row, offset by the number of columns specified in the iCol variable.
  9. This line has been amended from the original article. It now has an Exit For command, which exits the For Next loop and doesn’t process any more iCol numbers for this c variable.
  10. The code after the HandleExit: label performs all the tidying up required for the end of the macro. Basically it returns Excel to its normal state. The Exit Sub command stops the macro.
  11. The HandleError: label is only used when an error is encountered in the operation of the macro. When an error occurs a dialog box will be displayed with the words “Error encountered”.

The user will have to click an OK button and then the HandleExit: label commands, from note 10, will be performed and the macro stops.

Comments: I typically include more comments in my code. The two green lines at the top of Figure 1 are examples of comments. They don’t perform any tasks but are used to document, explain and describe aspects of the code. Since I was describing the macro in the article I left the comments out to save space in Figure 1. I have included more comments in the companion file.

Warning!

You cannot use the undo feature after running a macro. Running a macro clears the undo list. As a safeguard, I suggest saving the file just before running a macro, so if something doesn’t work you can simply close the file without saving and then revert to the previous version.

Note: The above macro won’t copy all cells if there are two, or more, consecutive blank cells in the range that is selected before the macro is run. Any cells below the two blank cells will not be copied. Note 9, and the line associated line in the code have been amended from the original article to enable the correct handling of selected ranges.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to a4@iinet.net.au.

This article is from the December 2014 issue of INTHEBLACK


December 2014
December 2014

Read the December issue

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.

Table of contents