The cells to the right of the first column will be verified as empty or containing only spaces. Screen Updating = False End Sub A worksheet function solution maintaining two columns, one with the original data and one with the rearrangement.
Sub Join() 'David Mc Ritchie 1998-08-05 [Ctrl j] documented in ' cells in selected portion of a row together 'can be used as a reversal of Data/Test2cols or Sep Term() Application. Calculation = xl Calculation Manual On Error Resume Next Dim i Rows as Long, m Row as Long, ir as Long, ic as Long i Rows = Selection. You could, of course, eliminate the dependence with copy, the Edit, paste special, values and then eliminate the original column.
Also see Trim ALL() to TRIM all cells in a selection, followed by Remove All Spaces to remove all spaces and char(160). Creates text data across multiple ranges (areas) to create test data containing text value of cell address followed by the area number for each cell in the selected ranges. A1-1, B1-1, B2-2, C2-2, D2-2 includes manual steps, and macro solutions to fill in empty cells below descriptive headers, so a table column can be sorted.
Some additional topics on this page, some are brief descriptions pointing to another page Have tried to limit the scope of Join() and Sep Term() by checking for the lastcell.
Open v File 'Set targetworkbook Set wb2 = Active Workbook 'For instance, copy data from a range in the first workbook to another range in the other workbook wb2. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Ensure you set back to True/xl Automatic at end of your programming If opening the second workbook is not going to cause performance issues, you may as well do it.
Speed and efficiency considerations can be seen in Proper, and other Text changes and in Slow Response. Generally to activate things you have changed the underlying formats. Also available on the code\page are Sep Last Name for people's names, Sep Last Word uses Str Reverse new in Excel 2000, Last Word function uses Str Reverse, resubstr function using VBA Script.Adjusting the column width would affect the product list that starts in row 12, so that's not an option. Screen Updating = True End If End Sub Screen updating is turned off while the code runs, and it all happens in the blink of an eye.Usually, if you add more text to a single cell, and Wrap Text is turned on, the row height automatically adjusts, to fit the text. To test the code, make a change to the text in the named merged cells, then press Enter. Is this something that you'll use in your workbooks? [Update: The original code is below, and there are several modified versions of the code in the comments. There is also an updated version of Smallman's code in this December 2015 blog post.] The merged cells are named Order Note, and that name will be referenced in the event code.