Dear all,

I've 1 column called Duration in my excel. I want to create a macro to check if duration is greater than 10 years, then delete the row. The duration is in range i.e 0-3, 5-10 and etc. I've created a macro but somehow it also delete rows with Duration less than 10 years. For this macro i formatted the Duration column as Text because if i set it to Number is will translate it into number i.e. 11-13 become 41579. Below I illustrate the scenario and my sample coding. Thanks in advance for any help.

Original data
------------
ColA ColB ColC
Item Code Duration
1 DE123 0-3
2 PK987 7-10
3 BG313 10-13
4 JJ123 5-7
5 AW100 12-15

Expected result
---------------
ColA ColB ColC
Item Code Duration
1 DE123 0-3
2 PK987 7-10
4 JJ123 5-7


Sample code
------------

Sub ExceedDuration()
Dim lr As Long
Dim R As Integer
Dim n As Integer

Application.ScreenUpdating = False
Set TestBook = ActiveWorkbook
Windows("Test.xls").Activate

Sheets("Sheet1").Select
Range("A2").Select
lr = Cells(Rows.Count, 1).End(xlUp).Row
n = 1

For R = 3 To lr
Sheets("Sheet1").Select
Range("A3").Select
FindString = Range("C" & R).Value
If (Range("C" & R).Value) "10" Then
Range("A" & R & ":C" & R).delete

n = n + 1

End If

Next R


End Sub

Hi,

Am Mon, 3 Dec 2012 04:43:00 +0000 schrieb ixara:

Original data
------------
ColA ColB ColC
Item Code Duration
1 DE123 0-3
2 PK987 7-10
3 BG313 10-13
4 JJ123 5-7
5 AW100 12-15

Expected result
---------------
ColA ColB ColC
Item Code Duration
1 DE123 0-3
2 PK987 7-10
4 JJ123 5-7

try:

Sub Test()
Dim LRow As Long
Dim i As Long
Dim intSearch As Integer

Application.ScreenUpdating = False
LRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = LRow To 2 Step -1
With Cells(i, 3)
intSearch = CInt(Left(.Text, InStr(.Text, "-") - 1))
If intSearch = 10 Then
Rows(i).Delete
End If
End With
Next
Application.ScreenUpdating = True
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2