Deleting Rows
 

Hi everyone,
I seem to be stuck again with a problem that probably is very easy.
The data in my sheet is spread over 7 Columns and about 200-300 Rows.
I want to loop through column 7 and delete the entire row if the value is
different to 6 possible values.
So if the value in column 7 is different from for example 428, 491, 492,
493, 495 and 496 then the entire row should be deleted.
I have tried putting these values in an array and do a comparison. That
didn't work out.
Neither did a comparison with 5 OR's.

Hope this is understandable, please let me know if you want to see the code
I've used.

Thanks for your help

KJ

Deleting Rows
 

one way

Sub deletenot()
x = Cells(Rows.Count, 1).End(xlUp).Row
For i = x To 4 Step -1
If Cells(i, 1) < 428 _
And Cells(i, 1) < 491 _
And Cells(i, 1) < 493 _
And Cells(i, 1) < 495 _
And Cells(i, 1) < 496 _
Then Cells(i, 1).EntireRow.Delete
Next
End Sub
--
Don Guillett
SalesAid Software

"KJ Dahl" wrote in message
...
Hi everyone,
I seem to be stuck again with a problem that probably is very easy.
The data in my sheet is spread over 7 Columns and about 200-300 Rows.
I want to loop through column 7 and delete the entire row if the value is
different to 6 possible values.
So if the value in column 7 is different from for example 428, 491, 492,
493, 495 and 496 then the entire row should be deleted.
I have tried putting these values in an array and do a comparison. That
didn't work out.
Neither did a comparison with 5 OR's.

Hope this is understandable, please let me know if you want to see the
code
I've used.

Thanks for your help

KJ

Deleting Rows
 

change the cells(i,1) to cells(i,7) for column 7

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
one way

Sub deletenot()
x = Cells(Rows.Count, 1).End(xlUp).Row
For i = x To 4 Step -1
If Cells(i, 1) < 428 _
And Cells(i, 1) < 491 _
And Cells(i, 1) < 493 _
And Cells(i, 1) < 495 _
And Cells(i, 1) < 496 _
Then Cells(i, 1).EntireRow.Delete
Next
End Sub
--
Don Guillett
SalesAid Software

"KJ Dahl" wrote in message
...
Hi everyone,
I seem to be stuck again with a problem that probably is very easy.
The data in my sheet is spread over 7 Columns and about 200-300 Rows.
I want to loop through column 7 and delete the entire row if the value
is
different to 6 possible values.
So if the value in column 7 is different from for example 428, 491, 492,
493, 495 and 496 then the entire row should be deleted.
I have tried putting these values in an array and do a comparison. That
didn't work out.
Neither did a comparison with 5 OR's.

Hope this is understandable, please let me know if you want to see the
code
I've used.

Thanks for your help

KJ

Deleting Rows
 

Hi KJ,

Try:

Sub DelRows()
Dim delRange As Range
Dim rCell As Range
Dim vArr As Variant
Dim blDelete As Boolean

vArr = Array(428, 491, 492, 493, 495, 496)

With ActiveSheet
For Each rCell In Intersect(.UsedRange, .Columns(7))
blDelete = False
On Error Resume Next
blDelete = Application.Match(rCell.Value, vArr, 0)
On Error GoTo 0
If blDelete Then
If Not delRange Is Nothing Then
Set delRange = Union(delRange, rCell.EntireRow)
Else
Set delRange = rCell.EntireRow
End If
End If
Next rCell
End With
If Not delRange Is Nothing Then
delRange.Delete
Else
MsgBox "No rows deleted"
End If
End Sub

---
Regards,
Norman



"KJ Dahl" wrote in message
...
Hi everyone,
I seem to be stuck again with a problem that probably is very easy.
The data in my sheet is spread over 7 Columns and about 200-300 Rows.
I want to loop through column 7 and delete the entire row if the value is
different to 6 possible values.
So if the value in column 7 is different from for example 428, 491, 492,
493, 495 and 496 then the entire row should be deleted.
I have tried putting these values in an array and do a comparison. That
didn't work out.
Neither did a comparison with 5 OR's.

Hope this is understandable, please let me know if you want to see the
code I've used.

Thanks for your help

KJ

Deleting Rows
 

Works brilliantly.
Thanks very much

kj

"Don Guillett" wrote in message
...
one way

Sub deletenot()
x = Cells(Rows.Count, 1).End(xlUp).Row
For i = x To 4 Step -1
If Cells(i, 1) < 428 _
And Cells(i, 1) < 491 _
And Cells(i, 1) < 493 _
And Cells(i, 1) < 495 _
And Cells(i, 1) < 496 _
Then Cells(i, 1).EntireRow.Delete
Next
End Sub
--
Don Guillett
SalesAid Software

"KJ Dahl" wrote in message
...
Hi everyone,
I seem to be stuck again with a problem that probably is very easy.
The data in my sheet is spread over 7 Columns and about 200-300 Rows.
I want to loop through column 7 and delete the entire row if the value is
different to 6 possible values.
So if the value in column 7 is different from for example 428, 491, 492,
493, 495 and 496 then the entire row should be deleted.
I have tried putting these values in an array and do a comparison. That
didn't work out.
Neither did a comparison with 5 OR's.

Hope this is understandable, please let me know if you want to see the
code
I've used.

Thanks for your help

KJ

Deleting Rows
 

Hi,

This should also work:

Sub deletenot2()
x = Cells(Rows.Count, 1).End(xlUp).Row
For i = x To 4 Step -1
Select Case Cells(i, 1)
Case 428, 491, 493, 495, 496
Case Else
Cells(i, 1).EntireRow.Delete
End Select
Next
End Sub

Regards,
KL
"KJ Dahl" wrote in message
...
Works brilliantly.
Thanks very much

kj

"Don Guillett" wrote in message
...
one way

Sub deletenot()
x = Cells(Rows.Count, 1).End(xlUp).Row
For i = x To 4 Step -1
If Cells(i, 1) < 428 _
And Cells(i, 1) < 491 _
And Cells(i, 1) < 493 _
And Cells(i, 1) < 495 _
And Cells(i, 1) < 496 _
Then Cells(i, 1).EntireRow.Delete
Next
End Sub
--
Don Guillett
SalesAid Software

"KJ Dahl" wrote in message
...
Hi everyone,
I seem to be stuck again with a problem that probably is very easy.
The data in my sheet is spread over 7 Columns and about 200-300 Rows.
I want to loop through column 7 and delete the entire row if the value
is
different to 6 possible values.
So if the value in column 7 is different from for example 428, 491, 492,
493, 495 and 496 then the entire row should be deleted.
I have tried putting these values in an array and do a comparison. That
didn't work out.
Neither did a comparison with 5 OR's.

Hope this is understandable, please let me know if you want to see the
code
I've used.

Thanks for your help

KJ

Deleting Rows
 

neater


--
Don Guillett
SalesAid Software

"KL" <lapink2000(at)hotmail.com wrote in message
...
Hi,

This should also work:

Sub deletenot2()
x = Cells(Rows.Count, 1).End(xlUp).Row
For i = x To 4 Step -1
Select Case Cells(i, 1)
Case 428, 491, 493, 495, 496
Case Else
Cells(i, 1).EntireRow.Delete
End Select
Next
End Sub

Regards,
KL
"KJ Dahl" wrote in message
...
Works brilliantly.
Thanks very much

kj

"Don Guillett" wrote in message
...
one way

Sub deletenot()
x = Cells(Rows.Count, 1).End(xlUp).Row
For i = x To 4 Step -1
If Cells(i, 1) < 428 _
And Cells(i, 1) < 491 _
And Cells(i, 1) < 493 _
And Cells(i, 1) < 495 _
And Cells(i, 1) < 496 _
Then Cells(i, 1).EntireRow.Delete
Next
End Sub
--
Don Guillett
SalesAid Software

"KJ Dahl" wrote in message
...
Hi everyone,
I seem to be stuck again with a problem that probably is very easy.
The data in my sheet is spread over 7 Columns and about 200-300 Rows.
I want to loop through column 7 and delete the entire row if the value
is
different to 6 possible values.
So if the value in column 7 is different from for example 428, 491,
492,
493, 495 and 496 then the entire row should be deleted.
I have tried putting these values in an array and do a comparison.
That
didn't work out.
Neither did a comparison with 5 OR's.

Hope this is understandable, please let me know if you want to see the
code
I've used.

Thanks for your help

KJ

Deleting Rows
 

I would be pleased to know why a "4" is needed in
For i = x To 4 Step -1

Merry X'mas

"Don Guillett" wrote in message
...
one way

Sub deletenot()
x = Cells(Rows.Count, 1).End(xlUp).Row
For i = x To 4 Step -1
If Cells(i, 1) < 428 _
And Cells(i, 1) < 491 _
And Cells(i, 1) < 493 _
And Cells(i, 1) < 495 _
And Cells(i, 1) < 496 _
Then Cells(i, 1).EntireRow.Delete
Next
End Sub
--
Don Guillett
SalesAid Software

"KJ Dahl" wrote in message
...
Hi everyone,
I seem to be stuck again with a problem that probably is very easy.
The data in my sheet is spread over 7 Columns and about 200-300 Rows.
I want to loop through column 7 and delete the entire row if the value
is
different to 6 possible values.
So if the value in column 7 is different from for example 428, 491, 492,
493, 495 and 496 then the entire row should be deleted.
I have tried putting these values in an array and do a comparison. That
didn't work out.
Neither did a comparison with 5 OR's.

Hope this is understandable, please let me know if you want to see the
code
I've used.

Thanks for your help

KJ

Deleting Rows
 

Not really needed. In this example, I was having the macro stop after
considering row 5. Change to 1 if you want it to goto the top.

--
Don Guillett
SalesAid Software

"PY&Associates" wrote in message
...
I would be pleased to know why a "4" is needed in
For i = x To 4 Step -1

Merry X'mas

"Don Guillett" wrote in message
...
one way

Sub deletenot()
x = Cells(Rows.Count, 1).End(xlUp).Row
For i = x To 4 Step -1
If Cells(i, 1) < 428 _
And Cells(i, 1) < 491 _
And Cells(i, 1) < 493 _
And Cells(i, 1) < 495 _
And Cells(i, 1) < 496 _
Then Cells(i, 1).EntireRow.Delete
Next
End Sub
--
Don Guillett
SalesAid Software

"KJ Dahl" wrote in message
...
Hi everyone,
I seem to be stuck again with a problem that probably is very easy.
The data in my sheet is spread over 7 Columns and about 200-300 Rows.
I want to loop through column 7 and delete the entire row if the value
is
different to 6 possible values.
So if the value in column 7 is different from for example 428, 491,
492,
493, 495 and 496 then the entire row should be deleted.
I have tried putting these values in an array and do a comparison.
That
didn't work out.
Neither did a comparison with 5 OR's.

Hope this is understandable, please let me know if you want to see the
code
I've used.

Thanks for your help

KJ

Deleting Rows
 

Thanks.
As I guessed please.

Regards

"Don Guillett" wrote in message
...
Not really needed. In this example, I was having the macro stop after
considering row 5. Change to 1 if you want it to goto the top.

--
Don Guillett
SalesAid Software

"PY&Associates" wrote in message
...
I would be pleased to know why a "4" is needed in
For i = x To 4 Step -1

Merry X'mas

"Don Guillett" wrote in message
...
one way

Sub deletenot()
x = Cells(Rows.Count, 1).End(xlUp).Row
For i = x To 4 Step -1
If Cells(i, 1) < 428 _
And Cells(i, 1) < 491 _
And Cells(i, 1) < 493 _
And Cells(i, 1) < 495 _
And Cells(i, 1) < 496 _
Then Cells(i, 1).EntireRow.Delete
Next
End Sub
--
Don Guillett
SalesAid Software

"KJ Dahl" wrote in message
...
Hi everyone,
I seem to be stuck again with a problem that probably is very easy.
The data in my sheet is spread over 7 Columns and about 200-300
Rows.
I want to loop through column 7 and delete the entire row if the
value
is
different to 6 possible values.
So if the value in column 7 is different from for example 428, 491,
492,
493, 495 and 496 then the entire row should be deleted.
I have tried putting these values in an array and do a comparison.
That
didn't work out.
Neither did a comparison with 5 OR's.

Hope this is understandable, please let me know if you want to see
the
code
I've used.

Thanks for your help

KJ