Solved Delete rows with specific dates using macro?

September 22, 2011 at 04:47:14
Specs: Windows Vista
Hello everybody,

I have 10 excel workbooks each with numerous worksheets within each, labeled 'stock 1' to 'stock 45' for example. Now within each worksheet, i would like to delete the rows which contain specific dates around christmas (ie. 24/12/##, 25/12/##, and 26/12/##). The dates start in column A row 3.

Does anyone out there now now to do this quickly via a macro of some sort, as doing it manually would take about six months i reckon!

Thanks very much.

See More: Delete rows with specific dates using macro?

Report •

September 22, 2011 at 08:15:26
✔ Best Answer
Try this in a backup copy of your workbook. Since it deletes rows, and can't be undone, test it in a backup copy first.

Sub NoMoreChristmas()
 For shtNum = 1 To Sheets.Count
  With Sheets(shtNum)
   lastRow = .Range("A" & Rows.Count).End(xlUp).Row
    For numRow = lastRow To 3 Step -1
     If Month(.Range("A" & numRow)) = 12 Then
        If Day(.Range("A" & numRow)) > 23 And _
           Day(.Range("A" & numRow)) < 27 Then
             .Range("A" & numRow).EntireRow.Delete
        End If
     End If
  End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

September 22, 2011 at 11:46:09
This does the job nicely. Cheers for all the help!

Report •

Related Solutions

Ask Question