Deleting Rows

M

markbarr

Board Regular
Joined
Aug 18, 2013
Messages
88
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
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Hi markbarr - It is important to note the excel saves 1/11/2013 as a number (41287). You can then use that number in a calculation. Any number (date) over that will be 41288, 41289 etc.

Check out the code below and let me know if it makes sense.

Happy excelling,

goesr

Code: 
Sub markbarr_Delete_Rows()
    Dim i As Integer 'Used to increment rows
    i = 1 'Start at row 1
    While Cells(i, 8).value <> ""
'    Cells(i, 8).Select
        If Cells(i, 8).value > 41287 Then
'            Cells(i, 8).EntireRow.Select
            Cells(i, 8).EntireRow.Delete
        i = i - 1
        End If
    i = i + 1
    Wend
End Sub
 
Upvote 0
Hi there

Thank you for the help

it certainly is a lot faster however it dosent delete all the dates > 1st november 2013. i think it has something tio do with the dates in the cells

in the columns some data just have ddmmyyyy others have the time information others are blank

for example one cell has 28/11/2013 with no time information when it is formatted as a date. when i change it to general format its value is 41606 so it is not greater than 41287 so is not deleted yet it is greater than the 1st of novemebr

if it will help i can post a link to my skydrive with an example
mark
 
Upvote 0
When deleting you need to start at the last cell and loop up.
ie in your original code
Code: 
 For s = r to 2 step -1
 
Last edited:
Upvote 0
Hi Mark

I tried that but still takes over 30 seconds for just 750 rows and it doesnt delete all the dates greater than 1st november

Mark
 
Upvote 0
Not home to post any code but try deleting the row ending select and turning off the screenupdating at the start I.e. Application. Screenupdating = False

I'll post something when I get in if you are still stuck as you might need to use INT or check that they are real dates
 
Last edited:
Upvote 0
Upvote 0
I'm still figuring out how i would code this.

Also, is this in a "table object" ??
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Filter & Delete Deleting Header Row Suddenly
Replies
0
Views
231
reberryjr
R
C
  • Question
Delete Cell Range, or Add Range and Copy Formulas etc. - Excel VBA
Replies
0
Views
71
CSTjr
C
D
  • Solved
User selects row, then move data in certain cells from selected row to certain cells in the row above
Replies
5
Views
237
jolivanes
jolivanes
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
131
gtd526
gtd526
SandsB
  • Question
How to NOT delete header row
Replies
2
Views
207
iggydarsa
iggydarsa

Forum statistics

Threads
1,215,045
Messages
6,122,830
Members
449,096
Latest member
Erald

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top