Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-17-2017, 10:04 PM
NoSparks NoSparks is offline deleting blanks and commas Windows 7 64bit deleting blanks and commas 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
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
pivot not showing number format with commas olybobo Excel 1 05-05-2016 05:09 PM
deleting blanks and commas Counting Blanks in 1 Column and Non-Blanks in Another dogwood705 Excel 4 02-07-2015 08:45 AM
Commas within fields in .csv files gar Excel 1 01-29-2015 09:24 PM
deleting blanks and commas How to use mid or left functions to seperate city,st,zip with no commas sinaranje Excel 4 11-11-2014 10:52 PM
deleting blanks and commas No inverted commas in word 2013. tonycrossley Word 3 01-27-2014 04:55 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:07 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft