Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have the following macro that removes Duplicates in a column. I would like to modify this macro so that it removes the dups in a column according to a change in another column. Say in column A I have 001 3 times and it has 30, 30, 30 in column B. I want to remove two of the 30's according to column A. When column A changes to 002 and it has 30, 30, 30 then I want the two 30's removed for 002.

    Sub RemoveDups()
    Dim rng As Range
    Dim i As Long
    Dim j As Long
    Dim N As Long
    On Error Resume Next
    Set rng = ActiveSheet.UsedRange.Columns("B:B")
    N = rng.Rows.Count
    For i = rng.Rows.Count To 2 Step -1
    For j = 1 To rng.Columns.Count
    If rng.Cells(i, j) = rng.Cells(i - 1, j) Then
    rng.Cells(i, j).ClearContents
    End If
    Next j
    Next i
    Set rng = Nothing
    End Sub

    I hope I have explained this adequately.
    Thanks for your help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The following macro does what you asked, but it may not be what you want:

    Code:
    Sub RemoveDups()
      Dim r As Long
      Cells(1, 1).CurrentRegion.Sort _
    	Key1:=Cells(1, 1), Key2:=Cells(1, 2), _
    	Header:=xlYes
      For r = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
    	If Cells(r, 1) = Cells(r - 1, 1) And Cells(r, 2) = Cells(r - 1, 2) Then
    	  Cells(r, 2).ClearContents
    	End If
      Next r
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you explain what the macro does. If I have client numbers 0001 (repeated 3 times) in column A with contract count of 50 in column B for each instance of the client number 0001 and then it changes to client number 0002 (repeated 3 times) in column A with contract count of 50 in column B for each instance of 0002. Do I have to specify the column to remove dups like the original macro does?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The macro clears items in column B if the values in columns A *and* B are the same as in the row above. If you want to work with different columns, you'll have to change the column numbers 1 and 2 to the appropriate values.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •