Non blank cells after removing duplicates?

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!

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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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 :)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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
Back
Top