Non blank cells after removing duplicates?

Thebatfink

Active Member
Joined
Apr 8, 2007
Messages
397
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!

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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
966
I encountered a similar problem, excel seems to remember which rows have been used... even though they are now empty!

I worked around it by using a "Find", to search for rows that do not contain text (using wildcards), and then returning the row number when that test is true.

Not the most elegant solution i fear, so i'm interested to see what suggestions the professionals make :)
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,647
UsedRange, xlDown and CountA can be unreliable when trying to find the last used row. Follow the link for an explanation.

You might try one of the following, including the Find function referenced by @Caleeco...

Code:
Sub LastRow()
' Find the last used row in a Column: column A in this example
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
End Sub

Code:
Sub LastRow1()
Dim LastRow As Long
LastRow = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
End Sub

In the LastRow1 macro, you can change the LookIn:=xlFormulas parameter to LookIn:=xlValues if that better suits your sheet.

Cheers,

tonyyy
 

Thebatfink

Active Member
Joined
Apr 8, 2007
Messages
397
But do these options cope with blank rows in the middle of data? This is the first time usedrange has not returned me the correct value.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,918
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top