98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Copy cell content while duplicate
Copy cell content while duplicate
Resolved · High Priority · Version 2007
Thu 6 Mar 2014, 13:35Delegate 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:58Trainer 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:23Trainer 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:23Trainer 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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Generating Random NumbersTo generate a random number in Excel use the = RAND() function. |
