Code to delete rows only works sometimes

[BartSeli]

Dear all,

I wrote the following code to copy a range of cells from one tab to another one (below a list) and delete a few rows based on the cell value of another column (and update a few pivots, hide a few rows in other tabs). The deleted rows are duplicates in the list on tab "RTC - Status en opvolging". I identify these duplicaties by using vlookup in the same range (which checks whether there is already the same ordernummer above the cells that have been pasted). If the value of this if(iserror(vlookup)-statement = 1, then the row should be deleted.

This code worked fine in the beginning, but now it seems that it does not delete the rows anymore even though the value in column K=1. Can anybody help me and point me out what's wrong with the code?

Thanks in advance,

Bart

    Sheets("Actual Arrivals").Select
    Range("D7:I7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("RTC - Status en opvolging").Select
    Range("E6").Select
    Selection.End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
    Dim rng As Range, cell As Range, del As Range
    Set rng = Intersect(Range("K:K"), ActiveSheet.UsedRange)
    For Each cell In rng
    If (cell.Value) = "1" _
    Then
    If del Is Nothing Then
    Set del = cell
    Else: Set del = Union(del, cell)
    End If
    End If
    Next cell
    On Error Resume Next
    del.EntireRow.Delete    Sheets("Pivots - Geplande bloktreinen").Select
    ActiveSheet.PivotTables("PivotsBloktreinen").PivotCache.Refresh
    Sheets("Pivots - Template wagonplanning").Select
    ActiveSheet.PivotTables("PivotTemplate1").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTemplate2").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTemplate3").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTemplate4").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTemplate5").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTemplate6").PivotCache.Refresh
    ActiveSheet.PivotTables("PivotTemplate7").PivotCache.Refresh
     
    Sheets("Template wagonplanning").Select
    Columns("A:C").Select
    Selection.EntireColumn.Hidden = False
    ActiveSheet.Cells.EntireRow.Hidden = False
    For Each cell In Range("Template1")
    If UCase(cell.Value) = "1" Then
    cell.EntireRow.Hidden = True
    Columns("B:B").EntireColumn.Hidden = True
    ActiveSheet.Cells.Range("A1").Select
    End If
    Next
    End Sub

[xladept]

All I can see is that you need to indicate a new line here:

del.EntireRow.Delete    Sheets("Pivots - Geplande bloktreinen").Select

del.EntireRow.Delete :  Sheets("Pivots - Geplande bloktreinen").Select

[BartSeli]

In the original code the lines are placed under each other, so I don't think this correction is necessary or am I wrong? I really can't figure out why it's not working anymore..If someone is willing to have a look at the original file, I will be glad to send it over.

Thanks in advance!

[xladept]

Hi BartSeli,

I tested this code:

Sub BartseliDel()
    
    Dim rng As Range, cell As Range, del As Range
    Set rng = Intersect(Range("K:K"), ActiveSheet.UsedRange)
    For Each cell In rng
    If (cell.Value) = "1" _
    Then
    If del Is Nothing Then
    Set del = cell
    Else: Set del = Union(del, cell)
    End If
    End If
    Next cell
    On Error Resume Next
    del.EntireRow.Delete
    
    End Sub

and it ran like a charm!

[BartSeli]

I can't figure it out either. Can it has something to do that the cell value of "1" is calculated in a table of excel 2010?

[xladept]

Have you single stepped through the code? I love the code by the way - did I rep you for that? - I intended to