Delete rows based on an array

[vbaker74]

So.. I have a large worksheet of information for which I need to
delete certain rows based on a list in another worksheet. I need a
delete row function that will look up the value in another worksheet
and delete the row based on that value. Can anyone help?

~Vic

 

[JLGWhiz]

Probably. If you would furnish the names or numbers of the sheets, the range
where the list is stored and which sheet it is in; and whether the data to be
compared in the other sheet is in a specific column or row and which it is;
or if it is radomly located within the sheet.

 

[Gary''s Student]

Let's say Sheet2 has the data and Sheet1 in column A has the list of rows we
want deleted from Sheet2. For example, in Sheet1 in A1 thru A4 we have:

1
5
7
11

The following macro will remove these rows from Sheet2:

Sub rowkiller()
Dim rkill() As Integer

Sheets("Sheet1").Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
ReDim rkill(1 To n)
For i = 1 To n
rkill(i) = Cells(i, 1).Value
Next

Sheets("Sheet2").Activate
Set rgkill = Cells(rkill(1), 1)
For i = 2 To n
Set rgkill = Union(rgkill, Cells(rkill(i), 1))
Next
rgkill.EntireRow.Delete
End Sub

 

[Ron de Bruin]

Hi Vic

See
http://www.rondebruin.nl/delete.htm

See the tip below the first example

If Not IsError(Application.Match(.Value, _
Array("jelle", "ron", "dave"), 0)) Then .EntireRow.Delete

'Or use this one with Application.Match if you want to check more values.
'in the cell. You can also use a range with the values to delete.
'Replace Array("jelle", "ron", "dave") with Sheets("Sheet1").Range("A1:A200")