Hello All,

I have a simple spreadsheet with approx 500 records. I would like to have
this data sorted by column D and delete all records that is outside of two
parameter dates. Question: how do I create a parameter field using VBA?



Naraine

Let's say your data is in cells D2100. There is a way to determine the
last row, but for now, I'll hard code it.

Sub Test()
Dim myRange As Range
Dim DeleteRange As Range
Dim r As Range

Set myRange = Range("D2100")

Set DeleteRange = Nothing
If r.Value > DateSerial(2007, 3, 1) Or r.Value < DateSerial(2007, 4, 1) Then
If DeleteRange Is Nothing Then
DeleteRange = r
Else
DeleteRange = Union(DeleteRange, r)
End If
End If

Application.DisplayAlerts = False
If Not DeleteRange Is Nothing Then
DeleteRange.EntireRow.Delete
End If
Application.DisplayAlerts = True
End Sub


Modify to suit. I've got it deleting the entire row. I'm not sure if
that's what you want or not.

HTH,
Barb Reinhardt
"Naraine Ramkirath" wrote:

> Hello All,
>
> I have a simple spreadsheet with approx 500 records. I would like to have
> this data sorted by column D and delete all records that is outside of two
> parameter dates. Question: how do I create a parameter field using VBA?
>
>
>
> Naraine
>
>
>
>
>

Thank Barb. is there a way for these two dates to be prompted for data
entry? e.g.

If r.Value > [?begdate] Or r.Value < [?enddate] Then
.....

"Barb Reinhardt" <(E-Mail Removed)> wrote in message
news:58BBA086-7205-46C9-BC92-(E-Mail Removed)...
> Let's say your data is in cells D2100. There is a way to determine the
> last row, but for now, I'll hard code it.
>
> Sub Test()
> Dim myRange As Range
> Dim DeleteRange As Range
> Dim r As Range
>
> Set myRange = Range("D2100")
>
> Set DeleteRange = Nothing
> If r.Value > DateSerial(2007, 3, 1) Or r.Value < DateSerial(2007, 4, 1)
Then
> If DeleteRange Is Nothing Then
> DeleteRange = r
> Else
> DeleteRange = Union(DeleteRange, r)
> End If
> End If
>
> Application.DisplayAlerts = False
> If Not DeleteRange Is Nothing Then
> DeleteRange.EntireRow.Delete
> End If
> Application.DisplayAlerts = True
> End Sub
>
>
> Modify to suit. I've got it deleting the entire row. I'm not sure if
> that's what you want or not.
>
> HTH,
> Barb Reinhardt
> "Naraine Ramkirath" wrote:
>
> > Hello All,
> >
> > I have a simple spreadsheet with approx 500 records. I would like to
have
> > this data sorted by column D and delete all records that is outside of
two
> > parameter dates. Question: how do I create a parameter field using VBA?
> >
> >
> >
> > Naraine
> >
> >
> >
> >
> >

I'd use something like this'

Dim BegDate As Date
Dim EndDate As Date

BegDate = InputBox("Enter Begin Date:", Date1)

EndDate = InputBox("Enter End Date:", Date2)


"Naraine Ramkirath" wrote:

> Thank Barb. is there a way for these two dates to be prompted for data
> entry? e.g.
>
> If r.Value > [?begdate] Or r.Value < [?enddate] Then
> .....
>
> "Barb Reinhardt" <(E-Mail Removed)> wrote in message
> news:58BBA086-7205-46C9-BC92-(E-Mail Removed)...
> > Let's say your data is in cells D2100. There is a way to determine the
> > last row, but for now, I'll hard code it.
> >
> > Sub Test()
> > Dim myRange As Range
> > Dim DeleteRange As Range
> > Dim r As Range
> >
> > Set myRange = Range("D2100")
> >
> > Set DeleteRange = Nothing
> > If r.Value > DateSerial(2007, 3, 1) Or r.Value < DateSerial(2007, 4, 1)
> Then
> > If DeleteRange Is Nothing Then
> > DeleteRange = r
> > Else
> > DeleteRange = Union(DeleteRange, r)
> > End If
> > End If
> >
> > Application.DisplayAlerts = False
> > If Not DeleteRange Is Nothing Then
> > DeleteRange.EntireRow.Delete
> > End If
> > Application.DisplayAlerts = True
> > End Sub
> >
> >
> > Modify to suit. I've got it deleting the entire row. I'm not sure if
> > that's what you want or not.
> >
> > HTH,
> > Barb Reinhardt
> > "Naraine Ramkirath" wrote:
> >
> > > Hello All,
> > >
> > > I have a simple spreadsheet with approx 500 records. I would like to
> have
> > > this data sorted by column D and delete all records that is outside of
> two
> > > parameter dates. Question: how do I create a parameter field using VBA?
> > >
> > >
> > >
> > > Naraine
> > >
> > >
> > >
> > >
> > >
>
>
>

Sub DeleteAllBut()
Dim myRange As Range
Dim Date1 As Date
Dim Date2 As Date
Dim NumRows As Integer
Dim Counter As Integer

Set myRange = Range("D2" &
ActiveSheet.Range("D65536").End(xlUp).Row)

NumRows = myRange.Rows.Count

'No error checking here. If you put in a weird date, Excel will try
to interpret
'whatever you type in and you could end up deleting everything.
Date1 = InputBox("Enter starting date: ", "Starting Date")
Date2 = InputBox("Enter ending date: ", "Ending Date")

For Counter = NumRows + 1 To 1 Step -1

If Range("D" & Counter).Value < Date1 Or Range("D" & Counter).Value >
Date2 Then
Range("D" & Counter).EntireRow.Delete
End If
Next Counter

End Sub

David,

works perfectly. Thank you!


"David Sisson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sub DeleteAllBut()
> Dim myRange As Range
> Dim Date1 As Date
> Dim Date2 As Date
> Dim NumRows As Integer
> Dim Counter As Integer
>
> Set myRange = Range("D2" &
> ActiveSheet.Range("D65536").End(xlUp).Row)
>
> NumRows = myRange.Rows.Count
>
> 'No error checking here. If you put in a weird date, Excel will try
> to interpret
> 'whatever you type in and you could end up deleting everything.
> Date1 = InputBox("Enter starting date: ", "Starting Date")
> Date2 = InputBox("Enter ending date: ", "Ending Date")
>
> For Counter = NumRows + 1 To 1 Step -1
>
> If Range("D" & Counter).Value < Date1 Or Range("D" & Counter).Value >
> Date2 Then
> Range("D" & Counter).EntireRow.Delete
> End If
> Next Counter
>
> End Sub
>

Barb,

thank you. it worked.
"Barb Reinhardt" <(E-Mail Removed)> wrote in message
news:52D8B4C5-6F9F-4EDF-8F1D-(E-Mail Removed)...
> I'd use something like this'
>
> Dim BegDate As Date
> Dim EndDate As Date
>
> BegDate = InputBox("Enter Begin Date:", Date1)
>
> EndDate = InputBox("Enter End Date:", Date2)
>
>
> "Naraine Ramkirath" wrote:
>
> > Thank Barb. is there a way for these two dates to be prompted for data
> > entry? e.g.
> >
> > If r.Value > [?begdate] Or r.Value < [?enddate] Then
> > .....
> >
> > "Barb Reinhardt" <(E-Mail Removed)> wrote in
message
> > news:58BBA086-7205-46C9-BC92-(E-Mail Removed)...
> > > Let's say your data is in cells D2100. There is a way to determine
the
> > > last row, but for now, I'll hard code it.
> > >
> > > Sub Test()
> > > Dim myRange As Range
> > > Dim DeleteRange As Range
> > > Dim r As Range
> > >
> > > Set myRange = Range("D2100")
> > >
> > > Set DeleteRange = Nothing
> > > If r.Value > DateSerial(2007, 3, 1) Or r.Value < DateSerial(2007, 4,
1)
> > Then
> > > If DeleteRange Is Nothing Then
> > > DeleteRange = r
> > > Else
> > > DeleteRange = Union(DeleteRange, r)
> > > End If
> > > End If
> > >
> > > Application.DisplayAlerts = False
> > > If Not DeleteRange Is Nothing Then
> > > DeleteRange.EntireRow.Delete
> > > End If
> > > Application.DisplayAlerts = True
> > > End Sub
> > >
> > >
> > > Modify to suit. I've got it deleting the entire row. I'm not sure
if
> > > that's what you want or not.
> > >
> > > HTH,
> > > Barb Reinhardt
> > > "Naraine Ramkirath" wrote:
> > >
> > > > Hello All,
> > > >
> > > > I have a simple spreadsheet with approx 500 records. I would like to
> > have
> > > > this data sorted by column D and delete all records that is outside
of
> > two
> > > > parameter dates. Question: how do I create a parameter field using
VBA?
> > > >
> > > >
> > > >
> > > > Naraine
> > > >
> > > >
> > > >
> > > >
> > > >
> >
> >
> >

> I have a simple spreadsheet with approx 500 records. I would like to
> have this data sorted by column D and delete all records that is
> outside of two parameter dates. Question: how do I create a parameter
> field using VBA?

Did you receive my e-mail on this?