Link to home
Start Free TrialLog in
Avatar of Jagwarman
Jagwarman

asked on

Excel 2010 delete rows with a dates less than last working day

Can an expert provide me with VBA code that will delete rows with a dates less than last working day

i.e. if today is 07-Mar-14 ...Cells in column A dated 05-Mar-14 or less delete Keep only 06-Mar-14

On Monday 10-Mar-14 keep only 07-Mar-14

Thanks
Avatar of Rgonzo1971
Rgonzo1971

HI,

pls try

Sub macro()
lstRow = Range("A" & Cells.Rows.Count).End(xlUp).Row
For Idx = lstRow To 1 Step -1 ' change 1 to the first row with dates
    If Cells(Idx, 1) < Application.WorksheetFunction.WorkDay(Date, -1) Then
        Cells(Idx, 1).EntireRow.Delete
    End If
Next
End Sub

Open in new window

Regards
Avatar of Jagwarman

ASKER

Hi Rgonzo1971 I am never sure what Dims to use?
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Brilliant thanks