By using listi.net you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes
   dbr983 : -2147217900 The INSERT INTO statement contains the following unknown field name: 'dt_accessed'. Make sure you have typed the name correctly, and try the operation again.insert into listaccess (dt_accessed,list_id,userid,ip_addr) values ('12/2/2021 3:36:15 AM',33459,-1,'34.207.247.69')

listi.net

 


How to delete duplicate rows?

Today I got a request to delete duplicate rows from a spreadsheet. The following code will do this.

Note that the first loop starts from the last record and works backwards. You do this because as you delete records the loop variable stays correct. If you went forward you wouldn't know where the end was. It assumes that there are 2,139 rows in the sheet and 16 columns. The sheet could be modified to figure out the last row and last column.

The code uses two for loops and checks field by field to see if the data is a duplicate.


Sub RemoveDups()

Dim i As Long, j As Long, bDup As Boolean

For i = 2139 To 1 Step -1 ' last row hardcoded
    If i = 1 Then Exit For 'to preve error reference row 0
   
    bDup = True
    Cells(i, 1).Select  
   
    For j = 1 To 16 ' each column last hard coded
       
        If Cells(i, j) = Cells(i - 1, j) Then
        Else
            bDup = False
            Exit For
        End If
       
           
    Next
    If bDup Then
        Rows(i).EntireRow.Delete
    End If
   


Next
blog comments powered by Disqus
   
Home | About | Feedback| Privacy | Terms of Service | @alecberg | FAQ | | Copyright © 2021 All Rights Reserved. Edit
This site uses cookies. If you don't want cookies and who doesn't want them as they're yummy, then leave.