Clear Duplicate rows

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Clear Duplicate rows

Post by adam »

Hi anyone,

Im trying to clear columns C,D,G,H,I where duplicate data is present. However im getting compile error "wrong number of arguments highlighting the line

Code: Select all

 Range(Cells(lngMyRow, 3), Cells(lngMyRow, 4), Cells(lngMyRow, 7), Cells(lngMyRow, 8)).ClearContents
Follwing is my code

Code: Select all

Sub Macro1()

    Dim lngMyRow As Long
    Dim lngMyCol As Long
    Dim lngLastRow As Long
    Dim objMyUniqueData As Object
    
    Application.ScreenUpdating = False

    lngLastRow = Range("C:I").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
    
    Set objMyUniqueData = CreateObject("Scripting.Dictionary")
    
    For lngMyRow = 30 To lngLastRow 'Assumes the data starts at row 1. Change to suit if necessary.
        If objMyUniqueData.Exists(CStr(Cells(lngMyRow, 3) & Cells(lngMyRow, 4) & Cells(lngMyRow, 7) & Cells(lngMyRow, 8))) = False Then
            objMyUniqueData.Add CStr(Cells(lngMyRow, 3) & Cells(lngMyRow, 4) & Cells(lngMyRow, 7) & Cells(lngMyRow, 8)), Cells(lngMyRow, 3) & Cells(lngMyRow, 4) & Cells(lngMyRow, 7) & Cells(lngMyRow, 8)
        Else
            Range(Cells(lngMyRow, 3), Cells(lngMyRow, 4), Cells(lngMyRow, 7), Cells(lngMyRow, 8)).ClearContents
        End If
    Next lngMyRow
    
    Set objMyUniqueData = Nothing
    
    Application.ScreenUpdating = True
    
    MsgBox "Any duplicates in Col's C,D,H & I have now been cleared.", vbInformation
    
End Sub
Any help would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Clear Duplicate rows

Post by HansV »

Range(...) either takes a single string as argument, or two arguments representing the top left and bottom right cell of a rectangular range.
Use this instead:

Code: Select all

            Range("C" & lngRow & ":D" & lngRow & ",G" & lngRow & ":I" & lngRow).ClearContents
Best wishes,
Hans

User avatar
p45cal
2StarLounger
Posts: 142
Joined: 11 Jun 2012, 20:37

Re: Clear Duplicate rows

Post by p45cal »

You can just replace Range with Union:
Union(Cells(lngMyRow, 3), Cells(lngMyRow, 4), Cells(lngMyRow, 7), Cells(lngMyRow, 8)).ClearContents

Realise that your message "Any duplicates in Col's C,D,H & I have now been cleared." does not tally with that line of code which is C,D,G,H.

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Clear Duplicate rows

Post by adam »

The code works fine if I have data in all the columns (C,D,G,H,I) of the duplicate row.

However, it doesnt work if there's no data in any one of the columns.

The idea is to clear columns C,D,G,H,I based on the duplicate entries in column D between the range 30:67 even if theres an empty column data in columns C,G,H,I.

How could I achieve this?

I've attached a sample workbook for your reference.
Clear Duplicates.xlsm
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Clear Duplicate rows

Post by HansV »

Isn't this what you want?

Code: Select all

Sub Macro1()
    Dim lngMyRow As Long
    Dim lngMyCol As Long
    Dim lngLastRow As Long
    Dim objMyUniqueData As Object
    
    Application.ScreenUpdating = False
    
    lngLastRow = Range("C:I").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If lngLastRow < 30 Then Exit Sub
    
    Set objMyUniqueData = CreateObject("Scripting.Dictionary")
    
    For lngMyRow = 30 To lngLastRow
        If objMyUniqueData.Exists(Cells(lngMyRow, 4).Value) = False Then
            objMyUniqueData(Cells(lngMyRow, 4).Value) = 1
        Else
            Union(Cells(lngMyRow, 3), Cells(lngMyRow, 4), Cells(lngMyRow, 7), _
                Cells(lngMyRow, 8), Cells(lngMyRow, 9)).ClearContents
        End If
    Next lngMyRow
    
    Set objMyUniqueData = Nothing
    
    Application.ScreenUpdating = True
    
    MsgBox "Any duplicates in Col's C,D,G,H & I have now been cleared.", vbInformation
End Sub
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Clear Duplicate rows

Post by Doc.AElstein »

Hi
Just a very minor point, out of interest, just in passing…
HansV wrote:
10 Oct 2020, 09:57
... or two arguments representing the top left and bottom right cell of a rectangular range.
.. I used to think that, probably because …_
_ a) I read it somewhere
_ b) Usually people use it like that
It appears that in the two argument syntax, you get returned a single rectangular range area, ( of contiguous cells ), that extends so that it is just big enough to include the two ranges given
So for example, this will fill in the range A1:C10, ( at least it does for me in Excel 2002, 2003, 2007, 2010 and 2013 )
Range("C4", "A1:A10").Value = "Phill In"

Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Clear Duplicate rows

Post by ChrisGreaves »

Doc.AElstein wrote:
11 Oct 2020, 16:58
It appears that in the two argument syntax, you get returned a single rectangular range area, ( of contiguous cells ), ...
Hi Alan.
Interesting observation.
(Now I have to find a use for this. Grrrr! :cranky: )
Cheers
Chris

Code: Select all

Sub test1()
    Range("B2", "C3").Value = "Alan"
End Sub
Sub test2()`
    Range("c3", "d6").Value = "Beowulf"
End Sub
Sub test3()
    Range("d6", "e7").Value = "Chris"
End Sub
Sub test4()
    Range("A1:B2", "f4").Value = "David"
End Sub
Sub test5()
    Range("A1:b2", Range("f4", "g8")).Value = "Ernst"
End Sub
Sub test6()
    Range("A1:b2", Range(Range("d6", "e7"), "g8")).Value = "Frederich"
End Sub


An expensive day out: Wallet and Grimace

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Clear Duplicate rows

Post by Doc.AElstein »

ChrisGreaves wrote:
11 Oct 2020, 17:31
....(Now I have to find a use for this. Grrrr! :cranky: )....

Code: Select all

Sub test1()
.....
Sub test6()
 Range("A1:b2", Range(Range("d6", "e7"), "g8")).Value = "Frederich"
...
I can’t think of any thing much use, yet, Chris.
I suppose that two argument syntax version of Range( ) is returning what would be the UsedRange. So if you are possibly going to use two single area ranges, and want to know what the UsedRange will be, then that would be one use.

Code: Select all

 Sub UsdedRangeToBeOrNotToBe()  '  http://www.eileenslounge.com/viewtopic.php?p=275972#p275972
Dim Rng2BorNot2B As Range
 Set Rng2BorNot2B = Range("C4", "A1:A10")
Dim Rws2BorNot2B As Long, Clms2BorNot2B As Long: Let Rws2BorNot2B = Rng2BorNot2B.Rows.Count: Let Clms2BorNot2B = Rng2BorNot2B.Columns.Count
 ' Not used yet, even though I "got it" by Setting it
 MsgBox Prompt:="UsedRange row count is " & UsedRange.Rows.Count & " and UsedRange column count is " & UsedRange.Columns.Count & " and UsedRange top left row is " & UsedRange.Row & ".  If all are 1, then that means I either just used someting in the first cell, or more likely, as in this case, I've not used the worksheet yet." & vbCr & vbLf & "If I do use my range then it will have a row count of " & Range("C4", "A1:A10").Rows.Count & " and a column count of " & Range("C4", "A1:A10").Columns.Count & "," & vbCr & vbLf & "(and isn't it nice that sincee the server change we have a horizontal scroll bar on the code window, so I can fill it with stuff, and you don't have to read it unless you feel the urge. I ceratainly don't very often.  ( I do occaisionally have an urge or two, but, like most sensible people, I don't read my long posts very often. Well I don't need to do I-they're so good and rich with info that one read and you know it all, and all your problems are solved. :) " & vbCr & vbLf & " :) ;)  )   )"
 ' Now use it
 Let Rng2BorNot2B.Value = "Usded"
 MsgBox Prompt:="UsedRange row count is " & UsedRange.Rows.Count & " and UsedRange column count is " & UsedRange.Columns.Count & ""
 ' "Un use it" - I delete it, so Excel trashes it and slides the cells to fill the hole, and puts new ones in the hole then caused at the spreadsheet perimeter.
 UsedRange.Delete
 MsgBox Prompt:="UsedRange row count is " & UsedRange.Rows.Count & " and Usedrange column count is " & UsedRange.Columns.Count & ""
End Sub
_.________________________________

I personally don’t like that two argument syntax version of Range( ) because,
_a) It often seems to cause problems when people don’t use it right
_b) I like the single argument syntax version of Range( ) ,
_b)(i) especially since I realised that the single argument syntax version of Range( ) lets you define multiple areas,
_b(ii) and also because it will accept in place of the simple address reference, the entire reference string including the full closed workbook path*** , which I often have already defined in my coding anyway, so its convenient to use. That way I use
Application.Range( )
In conjunction with the full reference path. That way there is no ambiguity on what range I am using, regardless of where my coding is.
( ***It does not work if the workbook is closed, but it accepts the full closed workbook reference with the workbook open )
_._________

Often the main reason for using the two argument syntax version of Range( ) seems to be when you are dealing with a column number rather than a column Letter. That is most likely what the OP was originally trying to do.
I have got in the habit of having a simple function in most of my files to get the column Letter, so then I can always use the single argument syntax version of Range( )

So for example, the original erroring code line of the OPs of this
Range(Cells(lngMyRow, 3), Cells(lngMyRow, 4), Cells(lngMyRow, 7), Cells(lngMyRow, 8)).ClearContents
Would be written something like this, when using the single argument syntax version of Range( ),
Range("C3,D3,G3,H3").ClearContents =
Range("" & CL(3) & lngMyRow & "," & CL(4) & lngMyRow & "," & CL(7) & lngMyRow & "," & CL(8) & lngMyRow & "").ClearContents

Code: Select all

 Sub ColumnLetterOneArgumentRange() '  http://www.eileenslounge.com/viewtopic.php?p=275972#p275972
Dim lngMyRow As Long: Let lngMyRow = 3
'  Range(Cells(lngMyRow, 3), Cells(lngMyRow, 4), Cells(lngMyRow, 7), Cells(lngMyRow, 8)).ClearContents  ---   error!!
'  Range("C3,D3,G3,H3").ClearContents                                                                   ---   works!!
 Range("" & CL(3) & lngMyRow & "," & CL(4) & lngMyRow & "," & CL(7) & lngMyRow & "," & CL(8) & lngMyRow & "").ClearContents

' Or like Range("Sheet1!C3,D3,G3,H3").ClearContents
 Application.Range("=" & ThisWorkbook.Worksheets.Item(1).Name & "!" & CL(3) & lngMyRow & "," & CL(4) & lngMyRow & "," & CL(7) & lngMyRow & "," & CL(8) & lngMyRow & "").ClearContents
' or like Range("=[Book2.xls]Sheet1!C3,D3,G3,H3").ClearContents
 Application.Range("=[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets.Item(1).Name & "!" & CL(3) & lngMyRow & "," & CL(4) & lngMyRow & "," & CL(7) & lngMyRow & "," & CL(8) & lngMyRow & "").ClearContents
' or like Range("='C:\Users\Elston\Documents\[Book2.xls]Sheet1'!C3,D3,G3,H3").ClearContents
 Application.Range("='" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" & ThisWorkbook.Worksheets.Item(1).Name & "'!" & CL(3) & lngMyRow & "," & CL(4) & lngMyRow & "," & CL(7) & lngMyRow & "," & CL(8) & lngMyRow & "").ClearContents
End Sub
Public Function CL(ByVal lclm As Long) As String '                                                                              http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980                 https://excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort-Last-Row?p=7214&viewfull=1#post7214
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function
Alan
Last edited by Doc.AElstein on 15 Oct 2020, 10:19, edited 4 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Clear Duplicate rows

Post by Doc.AElstein »

P.S.
( Just to help avoid confusion, Hans in post #2 is actually using the single argument syntax version of Range( ) , but just happens to be referencing two ranges in the single string. He is basically simplifying something like Range("C3,D3,G3,H3") to Range("C3:D3,G3:H3") . But both those are using the single argument syntax version of Range( ) to refer to the same two areas , although one refers to a range object of 4 areas and the other refers to a range object of 2 areas.
If you used the two argument syntax version of Range( ) , like Range("C3:D3”, “G3:H3") , then you would be referencing the single range area of C3:H3

Code: Select all

 Sub RngAreas()
' Single argumant syntax, Range( )
Dim Rng2A As Range, Rng4A As Range
 Set Rng2A = Range("C3:D3,G3:H3")
 MsgBox prompt:="Rng2A has " & Rng2A.Areas.Count & " Areas in it"
 Set Rng4A = Range("C3,D3,G3,H3")
 MsgBox prompt:="Rng4A has " & Rng4A.Areas.Count & " Areas in it"

' Two argumant syntax, Range( )
Dim Rng1A As Range
 Set Rng1A = Range("C3:D3", "G3:H3")
 MsgBox prompt:="Rng1A has just " & Rng1A.Areas.Count & " Area in it"

End Sub
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Clear Duplicate rows

Post by adam »

Thankyou very much for the help Hans. It worked very well.
Best Regards,
Adam