| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
| Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
| Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
| Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
| deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions | |||
Linear Mode
