Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 835
  • Last Modified:

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
0
Jagwarman
Asked:
Jagwarman
  • 2
  • 2
1 Solution
 
Rgonzo1971Commented:
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
0
 
JagwarmanAuthor Commented:
Hi Rgonzo1971 I am never sure what Dims to use?
0
 
Rgonzo1971Commented:
Hi,

Sub macro()
Dim lstRow As Long, Idx As Long
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
0
 
JagwarmanAuthor Commented:
Brilliant thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now