Macro to delete rows according to list

eliW · Mar 14, 2004

Regarding my question in,
http://216.92.17.166/board2/viewtopic.php?t=81182
I would appreciate some help.

I started to build a macro that will be activated in every worksheet separately, checking id numbers in worksheet called "del" and delete the same records in the active worksheet.

It seems to work right when just "marking" the records by "*"
but the sentence to delete the entire row doesn't seem to work properly.

Code:

Sub DelRecList()
Dim cel1, cel2 As Range

For Each cel1 In Range("a1:a100")
For Each cel2 In Worksheets("del").Range("a1:a5")

If cel1.Value = cel2.Value Then
'cel1.EntireRow.Delete <----------------------problem 
cel1.Value = "*"
End If

Next cel2
Next cel1

End Sub

In the active worksheet I have numbers from 1-100 in column A
In "del" I have a list of numbers like: 2,4,12,23,50

Thank you in advance,

Eli

Andrew Poulsom · Mar 14, 2004

If you use a For Each...Next construct to loop around a range and delete rows within the loop, Excel does not take account of the deleted rows, so cells are missed out.

Two options:

1. Loop backwards through the range (written freehand and not tested):

Code:

Sub DelRecList() 
   Dim x as Integer, cel2 As Range 
   With Range("A1:A100")
      For x = .Count To 1 Step -1
         For Each cel2 In Worksheets("del").Range("a1:a5") 
            If .Cells(x, 1).Value = cel2.Value Then 
               .Cells(x, 1).EntireRow.Delete
               Exit For
            End If 
         Next cel2 
      Next x 
   End With
End Sub

Notice that I exited the second loop when a match was found. This speeds up the process.

2. Mark the matches with a *, then AutoFilter for that and delete the visible rows.

eliW · Mar 14, 2004

Thank you Andrew.
It has been a great help!
Eli

Macro to delete rows according to list

eliW

Well-known Member

Andrew Poulsom

MrExcel MVP

eliW

Well-known Member

Similar threads

Share this page

Macro to delete rows according to list

eliW

Well-known Member

Andrew Poulsom

MrExcel MVP

eliW

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock