Coding Help

[steelmaster95]

Hello, I am trying to write some code that will look at a column of numbers, and delete the rows that contain a number that does match any of the numbers in a specific array.

For example, say I define an array as [1,2,3,4] and my column of numbers in excel as [1,2,3,4,5,6,7,8,9]. The code will delete the rows that do not match any of the numbers in the array, in this example, rows 5, 6, 7, 8, and 9 will be deleted. Here's what I have -

Private Sub WriteToACell()

Set WS = ActiveSheet

Dim SomeArray(3) As Integer

SomeArray(0) = 1
SomeArray(1) = 2
SomeArray(2) = 3
SomeArray(3) = 4


For i = 1 To 10 Step 1
For j = 0 To 3 Step 1
If WS.Cells(i, 1).Value = SomeArray(j) Then [ Next i ]
ElseIf WS.Cells(i, 1).Value <> SomeArray(j) Then WS.EntireRow.Delete
Next j
Next i

End Sub


I'm am trying to compare each cell to the array by cycling through the array, but it doesn't seem to be working. Any help is appreciated!

[MarvinP]

Hi steelmaster,

There is a trick to delete rows using the For Next loop.

You need to start at the bottom and move up using "Step -1" at the end of the For statement.

When you delete rows and move down, you miss some rows.

I hope this hint is enough for you to see how to do it.

[steelmaster95]

Good hint

However the problem of comparing each cell to the entire array still alludes me, I'm not able to find the proper syntax online. Here's what I have -

Private Sub WriteToACell()

Set ws = ActiveSheet

Dim SomeArray(3) As Integer

SomeArray(0) = 1
SomeArray(1) = 2
SomeArray(2) = 3
SomeArray(3) = 4


Last = Cells(Rows.Count, 1).End(xlUp).Row
For i = Last To 1 Step -1
If ws.Cells(i, 1).Value <> SomeArray(0) Then ws.Cells(i, 1).EntireRow.Delete
Next i

End Sub


As you can see I am comparing each cell to SomeArry(0) at the moment (which is 1) as a placeholder for now.

Thanks!

[MarvinP]

OK SteelMaster,

Try this:

Please Login or Register  to view this content.

Del Row If Not in Arry.xlsm

[YasserKhalil]

Another version ..

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[bakerman2]

@ Yasser

The numbers in the array should be the numbers that remain on the worksheet. That said.

Please Login or Register  to view this content.

PS. Good use of Application.Match and Arrays. [thumb]

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )