Want to Delete Date and Data

xeeshanxp

New Member
Joined
Jun 22, 2010
Messages
25
I have excel sheet which got 3500 lines of data. I want to delete rows which got date less than 30-06-2010.This macro deleteing some dates but not all of them i change the value of last to 400 but it just delete few in that iteration


Sub Test()
Dim last As Integer
Dim DaTD As Date
DaTD = DateSerial(2010, 6, 30)

last = 100

For c = 1 To last
If Range("L" & c).Value <= DaTD Then
Rows(c).EntireRow.Delete

End If

Next c


End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You need to loop backwards. Try

Code:
Sub Test()
Dim last As Long, c As Long
Dim DaTD As Date
DaTD = DateSerial(2010, 6, 30)

last = Range("C" & Rows.Count).End(xlUp).Row

For c = last To 1 Step -1
    If Range("L" & c).Value <= DaTD Then
        Rows(c).EntireRow.Delete

    End If

Next c


End Sub
 
Upvote 0
If you've got 3500 lines of data then you need to change the "last" variable

last = 3500

The code was only looping 100 times then stopping, so you had 3400 lines still unchecked.
 
Upvote 0
No that code is not working at all now its not deleting a single row


Dan i know this thanks for letting me know
 
Upvote 0
Sorry, it should be

Rich (BB code):
last = Range("L" & Rows.Count).End(xlUp).Row
 
Upvote 0
Thanks its sorted ;)

I am using this code where K is column


ElseIf LCase(Left$(Range("K" & c).Value, 5)) = "khan" Then
Rows(c).EntireRow.Delete
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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