Thebatfink
Active Member
- Joined
- Apr 8, 2007
- Messages
- 410
Hi,
I'm removing duplicates from a range and then trying to find the last used row of the new data. But for some reason, it always returns the last used row prior to the duplicate removal. For instance I have 6000 rows of data which goes down to 20 after removal. But the lastused row still reports 6000?
How can I correct that? Thanks!
I'm removing duplicates from a range and then trying to find the last used row of the new data. But for some reason, it always returns the last used row prior to the duplicate removal. For instance I have 6000 rows of data which goes down to 20 after removal. But the lastused row still reports 6000?
How can I correct that? Thanks!
Code:
Worksheets("Data").Range("K2:K" & EnumerateDataRecs).Copy Worksheets("CSRs").Range("A1")
Worksheets("CSRs").Range("A1:A" & EnumerateCSRRecs).RemoveDuplicates Columns:=1, Header:=xlNo
Worksheets("CSRs").Sort.SortFields.Clear
Worksheets("CSRs").Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With Worksheets("CSRs").Sort
.SetRange Range("A1:A" & EnumerateCSRRecs)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Worksheets("CSR Specific").ComboBox21.ListFillRange = "=CSRs!A1:A" & EnumerateCSRRecs
Public Function EnumerateCSRRecs() As Long
EnumerateCSRRecs = Worksheets("CSRs").UsedRange.Rows.Count + Worksheets("CSRs").UsedRange.Rows(1).Row - 1
End Function