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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.