PDA

View Full Version : DELETE ROWS BASED ON DATE RANGE



pmari
09-16-2011, 05:15 PM
Hi Friends,

My worksheet having a cloumn ( "M") dates. No.of rows not defined and having blank rows also. Date in the form with time as shows 05-10-2011 23:59:00.

Ship Date
10-05-11
09-16-11
09-05-11

09-02-11
04-08-11
08-01-11
09-02-12
10-03-11

11-01-11
11-15-11
01-02-12
02-01-12

i need to have rows falling range say September '11 & October'11.

I need a VBA code.

Thanks in advance.

Bob Phillips
09-17-2011, 03:16 AM
Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 2 Step -1

If Month(.Cells(i, "A").Value) = 9 Or Month(.Cells(i, "A").Value) = 10 Then

'do nothing
Else

.Rows(i).Delete
End If
Next i
End With
End Sub

pmari
09-17-2011, 06:44 AM
Dear Sir,

Amazing. Thanks a lot.

pmari
09-17-2011, 11:26 AM
Dear Sir,

Amazing. Thanks a lot. .Also one more query , if i need to retain only sep '11 & Oct '11. Please change the code accordingly.

Excuse me for not asking my question clearly ..

Bob Phillips
09-17-2011, 12:01 PM
Sorry, forgot the year


Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 2 Step -1

If Year(.Cells(i, "A").Value) = 2011 And (Month(.Cells(i, "A").Value) = 9 Or Month(.Cells(i, "A").Value) = 10) Then

'do nothing
Else

.Rows(i).Delete
End If
Next i
End With
End Sub

pmari
09-17-2011, 12:14 PM
Thanks...Thanks...Thanks...:thumb

pmari
09-19-2011, 06:54 AM
Thanks...Thanks...Thanks...:thumb


Dear XLD.
One more query , it takes lot of time when no.of rows 10000. Is there any way to speed up.

pmari
09-20-2011, 07:58 AM
Dear XLD.
One more query , it takes lot of time when no.of rows 10000. Is there any way to speed up.


Hi XLD,

Turning off screen updating at the beginning (Application.ScreenUpdating = False) and turning it back on at the end (Application.ScreenUpdating = True).

Solved the problem.

Thanks again for the code.

justdriving
09-20-2011, 01:38 PM
No doubt, he is one of Genius members here.

Bob Phillips
09-20-2011, 02:17 PM
If you still want/need it to be faster, we could try a filter delete.

pmari
09-20-2011, 05:52 PM
If you still want/need it to be faster, we could try a filter delete.

Hi XLD,

This is OK.

But for learning, can you guide me how to use filter delete...

Bob Phillips
09-21-2011, 02:50 AM
Glad you asked :). This was 3- times faster in my tests



Sub ProcessData2()
Dim rng As Range
Dim Lastrow As Long
Dim i As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns(2).Insert
.Range("B1").Value = "tmp"
.Range("B2").Resize(Lastrow - 1).Formula = "=AND(MONTH(A2)<>9,MONTH(A2)<>10)"
Set rng = .Range("A1").Resize(Lastrow, 2)
rng.AutoFilter Field:=2, Criteria1:="=TRUE"
On Error Resume Next
Set rng = .Range("B2").Resize(Lastrow - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
.Columns(2).Delete
End With
End Sub

pmari
09-21-2011, 06:20 AM
Hi Boss,

It works well and faster.. Thanks again.:thumb