Deleting certain rows on conditions

[enfantter]

Hi all,
Im trying to create a userform which can erase certain rows in another sheet based on criterions.
I've got something like this:
Private Sub CommandButton1_Click()
Dim sletstart As Date
Dim sletslut As Date

Range("C3:J33").Interior.ColorIndex = xlNone
Range("o3:v33").Interior.ColorIndex = xlNone
Range("aa3:ah33").Interior.ColorIndex = xlNone
Range("am3:at33").Interior.ColorIndex = xlNone
Range("ay3:bf33").Interior.ColorIndex = xlNone
Range("bk3:br33").Interior.ColorIndex = xlNone
Range("bw3:cd33").Interior.ColorIndex = xlNone
sletstart = CDate(sletstart.Text)
sletslut = CDate(sletslut.Text)
Sheets("Data").Select
Count = UsedRange.Rows.Count 'WorksheetFunction.CountIf(Range(Cells(2, 1), Cells(502, 1)), medl.Text)
For j = 0 To UsedRange.Rows.Count
If Cells(j + 2, 1).Value = medl.Text And Cells(j + 2, 2).Value >= sletstart Then
Rows(j + 2).Select
Selection.Delete Shift:=xlUp
Count = Count - 1
End If
If Count = 0 Then Exit For
Next j
End Sub

where sletstart is a date

anyone?!

[tstav]

Hi enfantter,
Apart from the code, what seems to be the problem?
Do you get an error? Which error? On what line?
Anything...

[enfantter]

first of all i get that the thing with sletstart = ... is an invalid qualifier
but even if i get around that the code doesnt seem to delete based on both conditions, it just takes the first condition and then erases every other ..

[tstav]

first of all i get that the thing with sletstart = ... is an invalid qualifier
It would help if you gave us the accurate error message. Are you sure it doesn't say "Run-time error '13': Type mismatch"?

it just takes the first condition and then erases every other
You mean it deletes one row, skips the next, deletes the next, skips the next and so on?
Please don't leave us guessing.

[enfantter]

sorry - would you like the book?!

[enfantter]

here it its ...
im pressing the right button and entering the conditions in that form in order to delete in the sheet "data"

about the deleting - exactly!

[tstav]

.

[tstav]

Well enfantter,
If "you can't find the time to answer to a simple question" & _
"on messages you have already come across while testing your work" And _
"instead, you think it would be easier for you to have us " & _
"read Swedish, and go through a whole project" Then
'I'm out
End

[Bob Phillips]

Private Sub CommandButton1_Click()
Dim dteSletstart As Date
Dim dteSletslut As Date
Dim cnt As Long

With Sheets("Oversigt")

.Range("C3:J33").Interior.ColorIndex = xlNone
.Range("o3:v33").Interior.ColorIndex = xlNone
.Range("aa3:ah33").Interior.ColorIndex = xlNone
.Range("am3:at33").Interior.ColorIndex = xlNone
.Range("ay3:bf33").Interior.ColorIndex = xlNone
.Range("bk3:br33").Interior.ColorIndex = xlNone
.Range("bw3:cd33").Interior.ColorIndex = xlNone
End With

dteSletstart = CDate(sletstart.Text)
dteSletslut = CDate(sletslut.Text)

With Sheets("Data")

cnt = .UsedRange.Rows.Count 'WorksheetFunction.CountIf(Range(Cells(2, 1), Cells(502, 1)), medl.Text)
For j = 0 To .UsedRange.Rows.Count
If .Cells(j + 2, 1).Value = medl.Text And .Cells(j + 2, 2).Value = dteSletstart Then
Rows(j + 2).Delete Shift:=xlUp
cnt = cnt - 1
End If
If cnt = 0 Then Exit For
Next j
End With
End Sub

[enfantter]

thanx xld!

[enfantter]

really great solution,
i have one problem still though. The part that erases, skips one every time if i change the code to this,
With Sheets("Data")

cnt = .UsedRange.Rows.Count
For j = 0 To .UsedRange.Rows.Count
If .Cells(j + 2, 1).Value = medl.Text And .Cells(j + 2, 2).Value >= dteSletstart Then
Rows(j + 2).Delete Shift:=xlUp
cnt = cnt - 1
End If
If cnt = 0 Then Exit For
Next j
End With


I guess i can fix by doing
for ...
next ...
but is there a reason for this flaw, and is there a smarter way to fix it?!

[Bob Phillips]

With Sheets("Data")

cnt = .UsedRange.Rows.Count
For j = .UsedRange.Rows.Count To 0 Step -1
If .Cells(j + 2, 1).Value = medl.Text And .Cells(j + 2, 2).Value >= dteSletstart Then
Rows(j + 2).Delete Shift:=xlUp
cnt = cnt - 1
End If
If cnt = 0 Then Exit For
Next j
End With

[Aussiebear]

Is there any reason why we can't strip the .range lines to just one?


With Sheets("Oversigt")

.Range("C3:J33, o3:v33, aa3:ah33, am3:at33, ay3:bf33,_
bk3:br33, bw3:cd33").Interior.ColorIndex = xlNone
End With