Delete rows from the bottom with the last different date

[Mux99]

Hi all

Is it possible to create a macro to delete the last rows from the bottom of the worksheet with a different date? In the example below I would need it to select and delete from rows 1161 to the end. The data will always be in column A and there will be multiple dates (probably 7 or 14) with different times.

Thanks in advance

 

[MickG]

[COLOR="Navy"]Sub[/COLOR] MG16Apr57
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dt [COLOR="Navy"]As[/COLOR] Date, nDt [COLOR="Navy"]As[/COLOR] Date
Lst = Range("A" & Rows.Count).End(xlUp).Row
Dt = Int(Cells(Lst, 1)): nDt = Dt
[COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] nDt = Dt
    Cells(Lst, 1).ClearContents
    Lst = Lst - 1
    nDt = Format(Cells(Lst, 1), "dd/mm/yyyy")
[COLOR="Navy"]Loop[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

This may be better as I forgot to amend all of the code when I saw your date format !!!

[COLOR="Navy"]Sub[/COLOR] MG16Apr09
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dt [COLOR="Navy"]As[/COLOR] Date, nDt [COLOR="Navy"]As[/COLOR] Date
Lst = Range("A" & Rows.Count).End(xlUp).Row
Dt = Int(Cells(Lst, 1)): nDt = Dt
[COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] nDt = Dt
    Cells(Lst, 1).ClearContents
    Lst = Lst - 1
    nDt = Int(Cells(Lst, 1))
[COLOR="Navy"]Loop[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Mux99]

This may be better as I forgot to amend all of the code when I saw your date format !!!

[COLOR=Navy]Sub[/COLOR] MG16Apr09
[COLOR=Navy]Dim[/COLOR] Lst [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Dt [COLOR=Navy]As[/COLOR] Date, nDt [COLOR=Navy]As[/COLOR] Date
Lst = Range("A" & Rows.Count).End(xlUp).Row
Dt = Int(Cells(Lst, 1)): nDt = Dt
[COLOR=Navy]Do[/COLOR] [COLOR=Navy]While[/COLOR] nDt = Dt
    Cells(Lst, 1).ClearContents
    Lst = Lst - 1
    nDt = Int(Cells(Lst, 1))
[COLOR=Navy]Loop[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thanks for this. I replaced .ClearContents with .EntireRow.Delete and it worked great.

 

[MickG]

You're welcome