Copy cell content while duplicate

Resolved · High Priority · Version 2007

Thu 6 Mar 2014, 13:35 Delegate Carmen said...

Carmen has attended:
Excel VBA Intro Intermediate course

Copy cell content while duplicate

Hi,

I am trying to do a macro that finds duplicates in colum A and once they are found, it will copy some contents from the second duplicate cell into the first duplicate (in column C) and deleting the entire row after that.

So far I have found out how to detect the duplicates with the code below

Dim LastRowcheck As Long, n1 As Long
Dim DelRange As Range

With Worksheets("qrOUTPUT1")
LastRowcheck = .Range("A" & .Rows.Count).End(xlUp).Row

For n1 = 1 To LastRowcheck
If .Cells(n1, 1).Value = Cells(n1 + 1, 1).Value Then

End If

Next n1
End With

Could you assist with this?

Thank you

Mon 10 Mar 2014, 09:58 Trainer Doug said...

RE: copy cell content while duplicate

Hi Carmen

Interesting!
Just a question. Would it be possible to sort the list by the first column in ascending order?

Thanks
Doug

Mon 10 Mar 2014, 10:23 Trainer Doug said...

RE: copy cell content while duplicate

Hi again Carmen

If the A column can be sorted here is a solution using a For loop.
The macro starts at the bottom of the list and steps backwards.

Sub Dedupe()
Dim LastRowcheck As Long, n1 As Long
With Worksheets("qrOUTPUT1")
LastRowcheck = .Range("A" & Rows.Count).End(xlUp).Row
For n1 = LastRowcheck To 2 Step -1
If Cells(n1, 1) = Cells(n1 - 1, 1) Then
Cells(n1, 3).Copy Cells(n1 - 1, 3)
Rows(n1).Delete
End If
Next n1
End With
End Sub

I like the way you used the variable Lastcell.

Doug and Jens
Best STL

Mon 10 Mar 2014, 10:47 Delegate Carmen said...

RE: copy cell content while duplicate

Unfortunately I cannot sort the column. But let me have a look and let you know if it worked.

Thank you for your reply.

Carmen

Mon 10 Mar 2014, 10:23 Trainer Doug said...

RE: copy cell content while duplicate

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Generating Random Numbers

To generate a random number in Excel use the = RAND() function.

The value returned will always be between 0 and 1. To convert this to some other random value, you will need to multiply the result by the highest number you want to consider. For example, if you wanted a random number between 1 and 25, you could use the following code line:
= INT(25 * RAND()+ 1)

Since RAND() will always returns a value between 0 and 1 (but never 1 itself), multiplying what it returns by 25 and then using the Integer function INT on that result will return a whole number between 0 and 24.

Finally, 1 is added to this result, so that x will be equal to a number between 1 and 25, inclusive

View all Excel hints and tips