![]() The macro basically splits the range reference that is specified with the InputBox, and then creates the formula in the active cell. The macro makes it really fast to create the formulas. You can assign the macro to a button in the ribbon or keyboard shortcut. It then creates the Concatenate or Ampersand formula by creating an argument for each cell in the selected range. The Concatenate Macro uses an InputBox that allows you to select a range of cells. The following screencast shows the macro in action. So I wrote a macro that makes it possible to concatenate a range. Unfortunately there is no simple way to select the entire range you want to concatenate. It is a handy shortcut if you are concatenating a few cells, but it can still be time consuming if you are joining a lot of cells together. This is probably the fastest way to add multiple cells to your concatenate formula. You do NOT need the macro for this, it is built into Excel. CELLS OF THE SAME TYPE JOIN TOGETHER HOW TOThe following screencast shows how to use the Ctrl+Left-click shortcut. This saves time over having to type a comma after each cell selection. You can hold down the Ctrl key while selecting cells to add to the CONCATENATE formula. ![]() Option #1: Ctrl+Left-click to Select Multiple Cells This makes it difficult and time consuming to write the formulas if you have a lot of cells to join together (concatenate). However, you cannot concatenate a range of cells by referencing the range in the CONCATENATE function. One popular use is for creating VLOOKUP formulas based on multiple criteria. The CONCATENATE function can be very useful for combining values of multiple cells into one cell or formula. Skill level: Intermediate Concatenate: The Good & Bad This includes the Ctrl+left-click method, and a free VBA Macro that makes it quick & easy to create the concatenate or ampersand formulas. ![]() Bottom line: Learn two different ways to quickly (join) concatenate a range of cells. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |