lundi 5 octobre 2015

vba scan Col & Delete if 0 and then insert row if not equal to next row

This is what my data looks like. Its a summary from another sheet.

The code appears to run and do what I need, scan Co B and delete if there is a 0 value, scan Col I and insert row if row below is different, then repeat for Col H. However I get an "runtime 1004 application-defined or object defined error"message instead of the macro just ending. Open to any edits or suggestions

Range("A100000").End(xlUp).Activate
Range("N1") = ActiveCell.Row

For lRow = Cells(Cells.Rows.Count, "b").End(xlUp).Row To 1 Step -1

If Cells(lRow, "b") = 0 Then
            Rows(lRow).EntireRow.Delete

End If
Next lRow

For lRow = Cells(Cells.Rows.Count, "I").End(xlUp).Row To 1 Step -1

If Cells(lRow, "I") <> Cells(lRow - 1, "I") Then <~~ debugger highlights this line or the other version of this eq below
            Rows(lRow).EntireRow.Insert

End If
Next lRow

For lRow = Cells(Cells.Rows.Count, "H").End(xlUp).Row To 1 Step -1

If Cells(lRow, "H") <> Cells(lRow - 1, "H") Then
            Rows(lRow).EntireRow.Insert

End If
Next lRow

Range("A1").Activate

Application.ScreenUpdating = True

End Sub 

Aucun commentaire:

Enregistrer un commentaire