Thread: [Solved] deleting blanks and commas
View Single Post
 
Old 03-17-2017, 10:04 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

Providing your data is sorted so the dupes are together this should do what you're asking.
Be sure to test on a copy of the file.

Code:
Sub RemoveDubeRows()
    Dim lr As Long, r As Long, wr As Long
Application.ScreenUpdating = False
'row to write to
wr = Sheets("dupesheet").Cells(Rows.Count, 1).End(xlUp).Row + 1
'rows to check for dupes
With Sheets("Sheet1")
    'last row
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    'start at bottom row and work up
    For r = lr To 2 Step -1
        If .Cells(r, 1) = .Cells(r - 1, 1) Then
            'compare the two rows
            '  credit Tim Williams ~~~ with great explanation at
            '  http://stackoverflow.com/questions/19395633/how-to-compare-two-entire-rows-in-a-sheet
            If Join(Application.Transpose(Application.Transpose(.Cells(r, 1).EntireRow.Value)), "") = _
               Join(Application.Transpose(Application.Transpose(.Cells(r - 1, 1).EntireRow.Value)), "") Then
                'copy and delete if equal
                .Cells(r, 1).EntireRow.Copy Sheets("dupesheet").Cells(wr, 1)
                .Cells(r, 1).EntireRow.Delete
                wr = wr + 1
            End If
        End If
    Next r
End With
Application.ScreenUpdating = True
End Sub
I'll never use pepper again
Reply With Quote