Creating Parameter Fields
 

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

Creating Parameter Fields
 

Let's say your data is in cells D2:D100. 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("D2:D100")

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

Creating Parameter Fields
 

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" wrote in message
...
Let's say your data is in cells D2:D100. 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("D2:D100")

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

Creating Parameter Fields
 

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" wrote in message
...
Let's say your data is in cells D2:D100. 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("D2:D100")

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

Creating Parameter Fields
 

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:D" &
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

Creating Parameter Fields
 

David,

works perfectly. Thank you!


"David Sisson" wrote in message
ps.com...
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:D" &
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

Creating Parameter Fields
 

Barb,

thank you. it worked.
"Barb Reinhardt" wrote in message
...
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" wrote in
message
...
Let's say your data is in cells D2:D100. 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("D2:D100")

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

Creating Parameter Fields
 

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?