Exclude date

[Basta1980]

Hi,

I have a sheet containing dates in column G and H. I want Excel to delete
the rows where data is matched from an inputbox and the data in column G and
H and doesn't qualify. I have a code (see below) that i got from an earlier
post, thing is that when I use the code it deletes all rows.

So if for instance if I need all rows to be deleted where the date in column
G is earlier than Reeks1 (i.e. 01/07/2007) and the date in Column H is later
than Reeks 2 (i.e. 30/06/2008), the code below deletes everything although I
have manualy checked the list and there is at least one row which meets the
specific criterai and therefore should not be deleted. I Checked the format
cells, even text to columns. But what am I doing wrong?

Private Sub CommandButton7_Click()
Dim lngLastRow As Long
Dim i As Long
Reeks1 = InputBox("Start")
Reeks2 = InputBox("Eind")

lngLastRow = Sheets("Ruwe data").Cells(Rows.Count, "G").End(xlUp).Row

For i = lngLastRow To 2 Step -1
If Format(Cells(i, "G"), "dd/mm/yyyy") >= Reeks1 And _
Format(Cells(i, "H"), "dd/mm/yyyy") < Reeks2 Then
Else
Rows(i).EntireRow.Delete Shift:=xlUp
End If
Next i

End Sub

 

[dmoney]

try using or instead of and

 

[DataHog]

You are using an unusual date format dd/mm/yyyy not the usual mm/dd/yyyy.

On your worksheet "Ruwe data",
select the Column G & H,
format cell - cateory: custom
& enter "dd/mm/yyyy" in the Type box.

Hope that helps.

J Knowles

 

[Basta1980]

Hi dmoney,

This doesn't work either.

try using or instead of and

 

[Basta1980]

Hi DataHog,

This 2 doesn't change the problem.

You are using an unusual date format dd/mm/yyyy not the usual mm/dd/yyyy.

On your worksheet "Ruwe data",
select the Column G & H,
format cell - cateory: custom
& enter "dd/mm/yyyy" in the Type box.

Hope that helps.

J Knowles

 

[Dave Peterson]

Untested, but it did compile:

Option Explicit
Private Sub CommandButton7_Click()
Dim lngLastRow As Long
Dim i As Long
Dim Reeks1 As Variant
Dim Reeks2 As Variant

Reeks1 = InputBox("Start")

If IsDate(Reeks1) Then
Reeks1 = CDate(Reeks1)
Else
MsgBox "Please try again and enter a date!"
Exit Sub
End If

Reeks2 = InputBox("End")
If IsDate(Reeks2) Then
Reeks1 = CDate(Reeks2)
Else
MsgBox "Please try again and enter a date!"
Exit Sub
End If

With Sheets("Ruwe data")
lngLastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
For i = lngLastRow To 2 Step -1
If .Cells(i, "G").Value >= Reeks1 _
And .Cells(i, "H").Value < Reeks2 Then
Else
.Rows(i).Delete
End If
Next i
End With

End Sub


If you really wanted to compare those formatted strings, I would think that
you'd want to format each value and use yyyymmdd as the format.

 

[DataHog]

Be sure you change the format for the whole columns (G & H) to custom -
dd/mm/yyyy.

To test, go to the bottom of your data in column G and enter a test date
26/02/2009, then select the auto fill feature (the lower right corner of the
cell - click and drag down) and drag down 10 or so cells, does it continue
with 27/02/2009, 28/02/2009, 01/03/2008, 02/03/2008 ..... If it does, you
got the format set correctly.

This code below works for me.

Sub CommandButton7_Click()
' highlight Col G & H, Format Cell - Custom - dd/mm/yyyy
Dim lngLastRow As Long
Dim i As Long
Dim Reeks1 As Variant
Dim Reeks2 As Variant
Reeks1 = InputBox("Start Date dd/mm/yyyy")
If Reeks1 = False Then Exit Sub
Reeks2 = InputBox("End Date dd/mm/yyyy")
If Reeks2 = False Then Exit Sub

lngLastRow = Sheets("Ruwe data").Cells(Rows.Count, "G").End(xlUp).Row

For i = lngLastRow To 2 Step -1
If Format(Cells(i, "G"), "dd/mm/yyyy") >= Reeks1 And _
Format(Cells(i, "H"), "dd/mm/yyyy") < Reeks2 Then
Else
Rows(i).EntireRow.Delete Shift:=xlUp
End If
Next i

End Sub

 

[DataHog]

Okay the other routine bombed - this really works this time.

I have just learned when using dimension as Date it must be mm/dd/yyyy format.

So I changed my code to:
Dim Reeks1 As Date
Dim Reeks2 As Date
and
If Format(Cells(i, "G"), "mm/dd/yyyy") >= Reeks1 And _
Format(Cells(i, "H"), "mm/dd/yyyy") < Reeks2 Then


So you must use format cells - standard date format mm/dd/yyyy on Column G & H

And this code will work.

Sub CommandButton7_Click()
Dim lngLastRow As Long
Dim i As Long
Dim Reeks1 As Date
Dim Reeks2 As Date
Reeks1 = Application.InputBox("Start Date", Default:=Range("G2").Value,
Type:=9)
If Reeks1 = False Then Exit Sub
Reeks2 = Application.InputBox("End Date",
Default:=Range("H1").End(xlDown).Value, Type:=9)
If Reeks2 = False Then Exit Sub
lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "G").End(xlUp).Row

For i = lngLastRow To 2 Step -1
If Format(Cells(i, "G"), "mm/dd/yyyy") >= Reeks1 And _
Format(Cells(i, "H"), "mm/dd/yyyy") < Reeks2 Then
Else
Rows(i).EntireRow.Delete Shift:=xlUp
End If
Next i

End Sub

 

[Leo Mazzi]

You are here comparing strings and not dates and that is the reason of the
wrong behavior.

Take two dates that I can show in your format (dd/mm/yyyy);

20/06/2008
and
15/07/2008

if you compare them as strings then the first is greater than the other

So the solution would be or to use a format whit which the operators < and >
function well (normally this is the standard ISO format: YYYYMMDD) or to
conver the user input to a date value and compare the dates as dates not as
string.

Best regards,
Leo