Run time error 1004, Application-defined or object-defined error.

[nmh7]

Hi all I am running into this error whenever I try to run my macro. My excel data has 2100 rows and 80 columns. Here is my code:

Sub MergeSameCell()

Dim Rng As Range, xCell As Range
Dim lastrow As Long
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

 

[jasonb75]

Which line is causing the error?

 

[nmh7]

The error occurs after this line: WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge

 

[Joe4]

Note that merging cells is probably one of the worst things you can do in Excel, and should really be avoided, if at all possible.
Merged cells wreak havoc with all sorts of things like sorting and VBA.

If you are just merging cells across single rows, a much better option is to use the "Center Across Selection" format option instead.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[nmh7]

Note that merging cells is probably one of the worst things you can do in Excel, and should really be avoided, if at all possible.
Merged cells wreak havoc with all sorts of things like sorting and VBA.

If you are just merging cells across single rows, a much better option is to use the "Center Across Selection" format option instead.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

I am trying to combine the duplicates in column A but want it to line up with the various rows that have that one like term in Column A. That way I can see which cell lines up with the others.

 

[nmh7]

Like this for example. Just with 80 columns ans 2000 rows

 

[Joe4]

So, its just a visual thing where you only want to each date once where there are a whole bunch of duplicate values?
If it is just a visual thing, this can be accomplished pretty easily using Conditional Formatting.

In your example, select cells A1:A12, then go to Conditional Formatting and select the "Use a formula to determine which cells to format" option.
Then enter this formula:

=$A1=$A2

Then go to the Font formatting tab, and in the Color box, select the white color.
Then, the duplicate text values will blend in with the background and not be visible.

 

[nmh7]

i am not sure if that would work for what I am trying to do. I can post my excel file here later today.

 

[nmh7]

Components used .xlsm

Shared with Dropbox

www.dropbox.com

In my file I need to combine the part numbers and then list the project number next to them so I can see what quantity of boards were used. At the end I need it to sum the total amount of parts used.