Creating Parameter Fields

[Naraine Ramkirath]

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

 

[Guest]

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]

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
.....

 

[Guest]

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)

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
.....

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

 

[David Sisson]

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

 

[Naraine Ramkirath]

David,

works perfectly. Thank you!

 

[Naraine Ramkirath]

Barb,

thank you. it worked.

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)

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
.....

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
:

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

 

[IanKR]

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?