VBA code to merge cells with consecutive duplicate entries in Excel

Tonight, I successfully tested VBA code posted here by Extend Office, and also copied below, which you can use to merge consecutive cells with duplicate entries in a column in Excel. So, if you have multiple entries of X in consecutive cells in a column, the macro will merge those cells together but it will not merge the first range of X with a subsequent range later on in the column.

In this example, we first merge the cells in column A with the same entry. After putting the code in new module, select the range, and then go to View . . . Module and run 'MergeSameCell'.

Consecutive duplicate entries are merged in column A, but in column B where the entries of 'New York' are not consecutive they are not merged.

In order for the macro to work correctly you need to select a limited range in a column. You can't select a whole column or $A:$A.

Sub MergeSameCell()

'Updateby20131127

Dim Rng As Range, xCell As Range

Dim xRows As Integer

xTitleId = "KutoolsforExcel"

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

Application.ScreenUpdating = False

Application.DisplayAlerts = False

xRows = WorkRng.Rows.Count

For Each Rng In WorkRng.Columns

For i = 1 To xRows - 1

For j = i + 1 To xRows

If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then

Exit For

End If

Next

WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge

i = j - 1

Next

Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

Find rows with duplicate data with conditional formatting in Excel

Don't miss that you can easily find not only where duplicate values appear in any range on a worksheet, (see the Tip of the Night for November 25, 2018) but also where values are equal across an entire row, using Conditional Formatting in Excel.

In this example, we want to find where more than player had the same number of home runs and runs batted in in the same season. In column E we enter the CONCATENATE formula, and then select the range of data for the year, HR, and RBI fields. Enter the formula, and then select the first cell, type a comma, select the next cell, type a comma, and then select the final cell and close the formula.

Then select the entire range of data in column E. Go to Home . . . Conditional Formatting . . . Highlight Cell Rules . . . Duplicate Values.

A dialog box will open allowing you to set the color of the duplicate values, or change to a review for unique values.

You can then simply filter in column E for the shaded cells, and the highlight the rest of the columns.

Disabling Formula Updates for Broken Links in Excel

In order to get around the problem of an Excel workbook attempting to update formulas by referencing outside spreadsheets it’s no longer linked to, it's necessary to force Excel to work with certain settings. If an outside linked to workbook is not present in the same linked to network location a #NAME error will occur.

If you already have a different Excel file open, with the default ‘Automatic’ setting activated, a setting of a new file to only manually update formulas will be overridden and formulas will automatically update, causing an error.

If the Excel files you’re working with themselves have the ‘automatic’ setting selected for formula calculation, the #NAME error will still occur, even if when they are opened one by one while all other workbooks have been closed.

To avoid this problem follow these steps:

1. Open a new blank workbook.

2. Go to File . . . .Options , then click ‘Formulas’ on the menu on the left. In the ‘Calculation options’ section select the radial button for ‘Manual’ under Workbook Calculation and uncheck the ‘Recalculate workbook before saving’. We need this option set so we can save the workbook on the worksheet that is used without having the formulas with broken links update.

3. On the left side menu, click on the Trust Center . . . and click ‘Trust Center Settings’

. . . you’ll see you have the option to ‘Disable all Data Connections’ and then to ‘Disable automatic update of Workbook links’ under the ‘External Content’ section.

Keep this blank workbook open, and then open each of the spreadsheets you want to review. They will inherit the settings from your 'priming' workbook.