user3781528 February 2016

Remove duplicate values within dynamic ranges identified by text strings

Text “endofdata” in col B identifies the boundaries of multiple ranges on a single sheet. I’m trying to step through each range and remove duplicate values in columns E and F within each range. I also call a routine that deletes blank rows that are generated when duplicates are removed. The bottom row with “endofdata” is always removed when .removeduplicates is executed.

I’ve tried the Do loop but it’s failing. (It works for the first range but fails for the next range) Please suggest how to make this work. What kind of loop should I use? How should I search for “endofdata” string? Thank you very much in advance.

enter image description here

Sub RemoveDupsinRange()


Dim LastRow As Long, i As Long, startRow, EndRow

Call setSheets

LastRow = wsQC.Cells(wsQC.Rows.Count, "A").End(xlUp).Row

Debug.Print LastRow

For i = LastRow To 1 Step -1

Do
    If wsQC.Cells(i, 2).Value = "endofdata" Then
          startRow = i

       End If
    i = i - 1
   Loop Until wsQC.Cells(i, 2).Value = "endofdata"
   EndRow = i
   i = i - 1

   Range(startRow & ":" & EndRow).Select
   Selection.removeduplicates Columns:=Array(5, 6), _
        Header:=xlNo

       Call DeleteBlanks

Next i

End Sub

Answers


Scott Holtzman February 2016

I just tested this loop and it worked.

Sub RemoveDupsinRange()

Dim LastRow As Long, i As Long, rStart As Range, rEnd As Range

Call setSheets

LastRow = wsQC.Cells(wsQC.Rows.Count, "A").End(xlUp).Row

Debug.Print LastRow

Set rEnd = wsQC.Cells(LastRow, 2)

For i = LastRow To 2 Step -1

    Do

        i = i - 1

        If wsQC.Cells(i, 2).Value = "endofdata" Then
            Set rStart = wsQC.Cells(i, 2)
        End If


    Loop Until wsQC.Cells(i, 2).Value = "endofdata"

    wsQC.Range(rStart.Offset(, -1), rEnd.Offset(, 4)).RemoveDuplicates Columns:=Array(5, 6), Header:=xlNo

    Set rEnd = rStart

    Call DeleteBlanks

Next i

End Sub

Post Status

Asked in February 2016
Viewed 3,519 times
Voted 5
Answered 1 times

Search




Leave an answer