none
Referring to arnage which has had its duplicates removed RRS feed

  • Question

  • i have copied a range from one worksheet to another. i have then removed the duplicates.

    here is my code so far:

    Worksheets("Input file").Range("A1:A71").Value = Worksheets("Builder").Range("B2:B72").Value
    
    'Removes duplicates
    
    'Set the range to evaluate to rng.
    Set rng = Range("A1:A71")
    
    'initialize i to 1
    i = 1
    
    'Loop for a count of 1 to the number of rows
    'in the range that you want to evaluate.
    For counter = 1 To rng.Rows.Count
    
    'If cell i in the range contains an "o",
    'delete the row.
        'Else increment i
        If rng.Cells(i) = "o" Then
            rng.Cells(i).Cells.Delete
        Else
        i = i + 1
        End If
    
    Next

    This leaves me with 26 cells, how do I then go about referring to that new range of cells?

    Monday, January 9, 2017 1:53 PM

Answers

  • a) Use Currentregion to get all contiguous cells around a single cell

    b) Use Removeduplicates to remove the duplicates, much faster and easier.

    BTW, If you want to delete lines manually, you have to go from the bottom to the top. Otherwise it is possible that you miss a row.

    Andreas.

    Sub Test()
      Dim R As Range
      
      'Refer to the current range
      Set R = Range("A1").CurrentRegion
      'Remove the duplicates
      R.RemoveDuplicates Columns:=Array(1), Header:=xlNo
    End Sub

    • Marked as answer by VBNovice01 Monday, January 9, 2017 9:26 PM
    Monday, January 9, 2017 6:30 PM