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.
Dim LastRow As Long, i As Long, startRow, EndRow
LastRow = wsQC.Cells(wsQC.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1
If wsQC.Cells(i, 2).Value = "endofdata" Then
startRow = i
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), _