Last row two times.

[soidog]

Hello,
This code works but:
After removing duplicates in column "A", I get a new last row in column "A" so I must "declaire" "lrow" again so dates in column "B" are not filled in to the first "lrow".
Is there a way around this?

Sub Start()


Dim ws As Worksheet
Dim lrow As Long


Set ws = ActiveSheet


[COLOR=#ff0000]lrow = ActiveSheet.Range("A" & Rows.count).End(xlUp).Row[/COLOR]
Application.ScreenUpdating = False


    ws.Range("B7", ws.Cells(lrow, "U")).ClearContents
    ws.Range("A7", ws.Cells(lrow, "A")).RemoveDuplicates Columns:=1, Header:=xlNo
       
[COLOR=#ff0000]lrow = ActiveSheet.Range("A" & Rows.count).End(xlUp).Row[/COLOR]
    ws.Range("B7", ws.Cells(lrow, "B")) = Date
    
Application.ScreenUpdating = True
    
End Sub

Thanks in advance.

 

[Joe4]

Do you really need to find the initial last row at all?
You can probably apply the Remove Duplicates functionality to the whole column (blank rows shouldn't affect it), i.e.

Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo

You can then calculate the lrow variable after removing the duplicates.

 

[soidog]

Hi Joe,
Thanks for your answer but that will not work. I have "stuff" above row 6 that must be there.
Row 6 have headers for columns "A:U" so data starts at row 7.

 

[Joe4]

You could still do it, using Rows.Count, i.e.

    ws.Range("A7", ws.Cells(Rows.Count, "A")).RemoveDuplicates Columns:=1, Header:=xlNo

 

[soidog]

Nice piece of code, Joe.
I will use this.
Thanks a ton!!!