0

I need to remove lines from an Excel file where the value nets to zero with another line. 1,000 lines out of 10,000 may end up being deleted.

I sort the lines by absolute value from largest to smallest (for now done manually, I can add this later), then iterating through the values and deleting the lines that net to zero.

I have written code to execute on 50 lines, however it doesn't work:

Sub delete_row()

    Dim activerow As Integer
    
    activerow = 2

    Do
            
        If ActiveSheet.Cells(activerow, 13).Select + ActiveCell.Offset(1, 0) = 0 Then
            
            Rows("activerow:activerow + 1").Select
            Selection.Delete Shift:=xlUp
                       
        Else: activerow = activerow + 1
            
        End If
            
    Loop Until activerow = 50

End Sub

I am new to VBA and programming.

Community
  • 1
  • 1
JoshZ
  • 3
  • 2

1 Answers1

1

as a beginning (explanations in comments):

Sub delete_row()
    Dim iRow As Long ' in Excel sheet you can handle up to 1 million row
                     ' Integer type reaches up to some 32 thousands
                     ' Long type allows for far more

    For iRow = Cells(Rows.Count, 13).End(xlUp).Row To 2 Step -1 ' step backwards from column M (column index 13) last not empty cell row index to 2
        If Cells(iRow, 13).Value + Cells(iRow + 1, 13) = 0 Then Cells(iRow, 13).EntireRow.Delete Shift:=xlUp
    Next
End Sub

when deleting rows you Always have to start from the last one and then loop bakwards, to avoid missing any line

you hardy really need to Select anything (look at This link )

HTH
  • 2,031
  • 1
  • 4
  • 10
  • Note that the solution provided starts at the bottom and works up. that is an important part when deleting rows. – SmileyFtW Apr 05 '20 at 13:57
  • @SmileyFtW, added the note in answer – HTH Apr 05 '20 at 13:59
  • This is a great start and will likely work perfectly for OP; for a technically better solution see [this post](https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba/59975507#59975507). – Hayden Moss Apr 05 '20 at 14:46
  • @HaydenMoss, `Union()` based methods have their drawbacks as well, when it comes to handling a large amount of rows. But should ever there be universal "technically better solution", then it would use a mix of Filters and Sorting – HTH Apr 05 '20 at 14:52
  • Thanks a lot everyone! @HaydenMoss I'm not quite ready for this just yet haha – JoshZ Apr 05 '20 at 14:55
  • 2
    @Josh, you are welcome. And … you're never ready if you never try… – HTH Apr 05 '20 at 14:57