data between the dates
 

I have a worksheet from which I have to select the rows where the setup date
is between 5/1/06 and 5/31/06 and delete all other rows. How can I achieve
that programmatically? Also, how can I apply multiple conditions to get the
same results. Thanks.
Purnima

data between the dates
 

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = DateSerial(2006, 4, 1) And _
Cells(i, "A").Value <= DateSerial(2006, 4, 31) Then
Rows(i).Delete
End If
Next i

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Purnima Sharma" wrote in message
...
I have a worksheet from which I have to select the rows where the setup
date
is between 5/1/06 and 5/31/06 and delete all other rows. How can I achieve
that programmatically? Also, how can I apply multiple conditions to get
the
same results. Thanks.
Purnima

data between the dates
 

Thanks a lot!
Purnima

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = DateSerial(2006, 4, 1) And _
Cells(i, "A").Value <= DateSerial(2006, 4, 31) Then
Rows(i).Delete
End If
Next i

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Purnima Sharma" wrote in message
...
I have a worksheet from which I have to select the rows where the setup
date
is between 5/1/06 and 5/31/06 and delete all other rows. How can I achieve
that programmatically? Also, how can I apply multiple conditions to get
the
same results. Thanks.
Purnima

data between the dates
 

Hi Bob,

The code doesn't work. What am I doing wrong? My code is as follows:

Sub Macro1()

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "H").Value < DateSerial(2006, 5, 1) And _
Cells(i, "H").Value DateSerial(2006, 5, 31) Then
Rows(i).Delete
End If
Next i


End Sub

Where column "H" is the setup date and I want all the rows to be deleted
except where col. H's value is in May. Column A is the loan number (non
blank) which determines end of the row. Thanks for your help.

Purnima

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = DateSerial(2006, 4, 1) And _
Cells(i, "A").Value <= DateSerial(2006, 4, 31) Then
Rows(i).Delete
End If
Next i

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Purnima Sharma" wrote in message
...
I have a worksheet from which I have to select the rows where the setup
date
is between 5/1/06 and 5/31/06 and delete all other rows. How can I achieve
that programmatically? Also, how can I apply multiple conditions to get
the
same results. Thanks.
Purnima

data between the dates
 

Change it all

Sub Macro1()

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "H").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "H").Value < DateSerial(2006, 5, 1) And _
Cells(i, "H").Value DateSerial(2006, 5, 31) Then
Rows(i).Delete
End If
Next i

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Purnima Sharma" wrote in message
...
Hi Bob,

The code doesn't work. What am I doing wrong? My code is as follows:

Sub Macro1()

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "H").Value < DateSerial(2006, 5, 1) And _
Cells(i, "H").Value DateSerial(2006, 5, 31) Then
Rows(i).Delete
End If
Next i


End Sub

Where column "H" is the setup date and I want all the rows to be deleted
except where col. H's value is in May. Column A is the loan number (non
blank) which determines end of the row. Thanks for your help.

Purnima

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = DateSerial(2006, 4, 1) And _
Cells(i, "A").Value <= DateSerial(2006, 4, 31) Then
Rows(i).Delete
End If
Next i

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Purnima Sharma" wrote in
message
...
I have a worksheet from which I have to select the rows where the
setup
date
is between 5/1/06 and 5/31/06 and delete all other rows. How can I
achieve
that programmatically? Also, how can I apply multiple conditions to
get
the
same results. Thanks.
Purnima

data between the dates
 

I am sorry, your previous code did work, but since it is a very long file, it
takes for ever. When I coded it to show the rowcount it was showing on the
status bar.

When I use your newer code, nothing happens. Probably, because there are lot
of null vales in that column, so I think it stops wherever it sees a blank
(considering it as end of the file). I have to use column A because that is
the only column where there are no null values. Since it is a very long file,
is there any way to avoid loop. Is there any way to write SQL to achieve the
same functionality. Thanks for all your help.

Purnima

"Bob Phillips" wrote:

Change it all

Sub Macro1()

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "H").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "H").Value < DateSerial(2006, 5, 1) And _
Cells(i, "H").Value DateSerial(2006, 5, 31) Then
Rows(i).Delete
End If
Next i

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Purnima Sharma" wrote in message
...
Hi Bob,

The code doesn't work. What am I doing wrong? My code is as follows:

Sub Macro1()

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "H").Value < DateSerial(2006, 5, 1) And _
Cells(i, "H").Value DateSerial(2006, 5, 31) Then
Rows(i).Delete
End If
Next i


End Sub

Where column "H" is the setup date and I want all the rows to be deleted
except where col. H's value is in May. Column A is the loan number (non
blank) which determines end of the row. Thanks for your help.

Purnima

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = DateSerial(2006, 4, 1) And _
Cells(i, "A").Value <= DateSerial(2006, 4, 31) Then
Rows(i).Delete
End If
Next i

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Purnima Sharma" wrote in
message
...
I have a worksheet from which I have to select the rows where the
setup
date
is between 5/1/06 and 5/31/06 and delete all other rows. How can I
achieve
that programmatically? Also, how can I apply multiple conditions to
get
the
same results. Thanks.
Purnima

data between the dates
 

You said it didn't work!

This might be quicker

Sub Macro1()
Dim rng As Range
Dim iLastRow As Long
Dim i As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "H").Value < DateSerial(2006, 5, 1) And _
Cells(i, "H").Value DateSerial(2006, 5, 31) Then
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then
rng.Delete
End If

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Purnima Sharma" wrote in message
...
I am sorry, your previous code did work, but since it is a very long file,
it
takes for ever. When I coded it to show the rowcount it was showing on the
status bar.

When I use your newer code, nothing happens. Probably, because there are
lot
of null vales in that column, so I think it stops wherever it sees a blank
(considering it as end of the file). I have to use column A because that
is
the only column where there are no null values. Since it is a very long
file,
is there any way to avoid loop. Is there any way to write SQL to achieve
the
same functionality. Thanks for all your help.

Purnima

"Bob Phillips" wrote:

Change it all

Sub Macro1()

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "H").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "H").Value < DateSerial(2006, 5, 1) And _
Cells(i, "H").Value DateSerial(2006, 5, 31) Then
Rows(i).Delete
End If
Next i

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Purnima Sharma" wrote in
message
...
Hi Bob,

The code doesn't work. What am I doing wrong? My code is as follows:

Sub Macro1()

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "H").Value < DateSerial(2006, 5, 1) And _
Cells(i, "H").Value DateSerial(2006, 5, 31) Then
Rows(i).Delete
End If
Next i


End Sub

Where column "H" is the setup date and I want all the rows to be
deleted
except where col. H's value is in May. Column A is the loan number
(non
blank) which determines end of the row. Thanks for your help.

Purnima

"Bob Phillips" wrote:

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value = DateSerial(2006, 4, 1) And _
Cells(i, "A").Value <= DateSerial(2006, 4, 31) Then
Rows(i).Delete
End If
Next i

End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Purnima Sharma" wrote in
message
...
I have a worksheet from which I have to select the rows where the
setup
date
is between 5/1/06 and 5/31/06 and delete all other rows. How can I
achieve
that programmatically? Also, how can I apply multiple conditions
to
get
the
same results. Thanks.
Purnima