Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro help (2000)

    I have the following macro that fills in the blanks:
    Sub FillBlanks()
    Dim rng As Range
    On Error Resume Next
    Set rng = ActiveSheet.UsedRange.Columns("A:C")
    With rng
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Copy
    .PasteSpecial xlPasteValues
    End With
    Set rng = Nothing

    End Sub

    How would I change this macro to do the reverse and take out all duplicates but the first?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Macro help (2000)

    Try this:

    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("A:C")
    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

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro help (2000)

    Works perfectly - Thanks

Posting Permissions

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