Deleting Rows

[markbarr]

Guys

whats the quickest way of deleting rows that are greater than a specified date

I have a column (H) that has a date in it

there are 750 rows.

I need to check each row and if the date in col H is greater than or equals to a specified date then the entire row is deleted.

I have tried the standard select the row if >= and then entire.row.delete but it takes over 2 minutes for only 750 records and it doesnt delete all the rows that are greater than the date

this is the code I have tried

MyCount = Application.CountA(Worksheets("Tempdata").Range("C:C"))
r = MyCount
v = CDate(Worksheets("Change").Range("A4").Value)
For s = 2 To r
t = CDate(Worksheets("Tempdata").Range("I" & s).Value)
If t >= v Then
Worksheets("Tempdata").Rows(s).Select
Worksheets("Tempdata").Rows(s).EntireRow.Delete
End If
Next s



Any ideas

Mark

 

[XLfriend777]

Hi markbarr - Try the code below. It now has screen updating off while running and it also records and displays in a message box the seconds that the macro ran. When testing this on 750 rows of sample data in Column I, like in your Tempdata tab, the macro ran in 1 second.

Hope this helps.

Happy Excelling,

goesr

Sub markbarr_Delete_Rows2()
Dim start_time, end_time
start_time = Now()
Application.ScreenUpdating = False
    Dim i As Integer 'Used to increment rows
    i = 2 'Start at row 1
    While i < 752
'    Cells(i, 9).Select
        If Cells(i, 9).value > Cells(1, 1).value Then
'            Cells(i, 9).EntireRow.Select
            Cells(i, 9).EntireRow.Delete
        i = i - 1
        End If
    i = i + 1
    Wend
Application.ScreenUpdating = True
end_time = Now()
MsgBox ("Your macro ran for " & DateDiff("s", start_time, end_time) & " seconds.")
End Sub

 

[XLfriend777]

Hi markbarr - The code below turns off ScreenUpdating and also shows the seconds the macro ran in a message box at the end. You put the number of the date in Cell A1, i.e. 41579 for 11/1/2013, and all rows after that date will be removed. This code on my sample 751 rows ran in 1 second.

Hope this helps.

Happy Excelling,

goesr

Sub markbarr_Delete_Rows2()
Dim start_time, end_time
start_time = Now()
Application.ScreenUpdating = False
    Dim i As Integer 'Used to increment rows
    i = 2 'Start at row 1
    While i < 752
        If Cells(i, 9).value > Cells(1, 1).value Then
            Cells(i, 9).EntireRow.Delete
        i = i - 1
        End If
    i = i + 1
    Wend
Application.ScreenUpdating = True
end_time = Now()
MsgBox ("Your macro ran for " & DateDiff("s", start_time, end_time) & " seconds.")
End Sub

 

[MARK858]

I am a bit confused as in the post you say you want to delete the dates greater than November 1st but in your finished sheet you have kept the dates greater than November 1st?

Try the codes below which should delete the dates greater than November 1st (obviously change the signs if different)

with the filter

Sub NOLOOP()
    Application.ScreenUpdating = 0
    With Sheets("Tempdata").Range("I1:I" & Range("I" & Rows.Count).End(xlUp).Row)
        .AutoFilter field:=1, Criteria1:=">41579"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete
    End With
    Sheets("Tempdata").ShowAllData
    Application.ScreenUpdating = 1
End Sub

looping

Sub loopit()
    Dim lr As Long, i As Long
    Application.ScreenUpdating = 0
    lr = Range("I" & Rows.Count).End(xlUp).Row
    For i = lr To 2 Step -1
        If Cells(i, "I").Value > 41579 Then
            Cells(i, "I").EntireRow.Delete
        End If
    Next
    Application.ScreenUpdating = 1
End Sub

 

[markbarr]

Sorry for the confusion mark

I need to do it both ways

Im basically trying to seperate the data into two diferent sheets so all the stuff before 1st nov goes in one sheet and all the stuff after goes in another

Mark

 

[MARK858]

That's fine I'll amend the filter code when I get in this afternoon (including the date)

 

[markbarr]

Hi Goesr

i ran both but they don't seem to change anything after running i still seem to have 750 rows of dtes

when i put a msgbox in both cells(1,1) and cells(i,9) are coming up as blank

sure im doing something really stupid but cant work it out


Mark

 

[markbarr]

Goesr

i put MsgBox Cells(1, 1).Value in the code just after i=2 in the code and it reports it as blank. Cells 1,1 (Cell A1 i presume is correct) has the date 1/11/2013 in it so i would expect it to say that in he msgbox

Wierd

Mark

 

[MARK858]

Mark, before I redo the filter code this afternoon are the dates in column I in your actual sheet as per the workbook in the link?
And are the sheetnames the same?

 

[markbarr]

Tempdata is where all the data is stored in both the sample and my main project workbook. Button and Finished sheets are just for illustration in the sample file


Mark

 

[MARK858]

I'll try again

into two diferent sheets

What is the name of the other sheet?

and again are the dates in column I as per the sample?