I'm trying to figure out the logic of merging/deleting duplicate rows in a spreadsheet.
the first 5 columns are typically the duplicates, but its the columns after that that could be blank....which would make it easier..BUT there may be values in there which are unique..
what I'm trying to do is to delete duplicate rows and retain the data in each column if there is any, so have a couple of duplicates is OK since I can count the values uniquely, but am trying to get rid of the blanks without losing the values!
edit: if the values are identical in within the column, then that the duplicate does not need to be kept (updated screenshot)
this is the code I've been playing with, but it's all kinds of wrong (subscript out range) and it didn't do much of anything!
Sub MergeRow() Dim LastRow As Long, Dim i As Long Dim LastCol As Long Dim Col As Long Dim delRow As Range LastRow = Range("A" & Rows.Count).End(xlUp).Row LastCol = Cells(1, Columns.Count).End(xlToLeft).Column Set delRow = Range("A" & LastRow) For i = LastRow To 3 Step -1 If Range("A" & i) = Range("A" & i - 1) Then For Col = 1 To LastCol If Cells(i - 1, Col) = "" Then Cells(i - 1, Col) = Cells(i, Col) Next Col delRow.EntireRow.Delete xlShiftUp End If Next Rw Set delRow = Range("A" & LastRow) End Sub
example screenshot of what I'm looking to do:[ATTACH=CONFIG]63643[/ATTACH]