Excel VBA loop for deleting a range when a cell has a 0-CodePudding

I'm trying to write a VBA loop in which the loop checks the cells in column M and if it finds a zero, it will delete the range "ii:Mi". I'm not that familiar with for loops so I thought I could get help here.

The code I came up with is as follows but I think it's missing something or it's too simple for the action:

Sub delete()

Dim i As Integer

For i = 3 To 100

If Mi = 0 Then
Range("Ii:Mi").Select
Selection.delete Shift:=xlUp
End If

Next

End Sub

Thanks for the answers, they're very much appreciated.

CodePudding user response：

If I got your goal, this should work. Thought it will clearly mess your data using the Delete function:

Option Explicit
Sub delete()
    
    'Change the "SheetName" for the actual sheet name
    'Assuming that sheet is in the same workbook as the code
    With ThisWorkbook.Sheets("SheetName")
        'Try to find the last row with data instead going
        'arbitrary 3 to 100
        Dim last_row As Long
        'this gets the last cell with data in column M, change as needed
        last_row = .Cells(.Rows.Count, "M").End(xlUp).Row
        'Store all the rows meeting your criteria in a single variable
        Dim range_to_delete As Range
        'loop through the column
        Dim C As Range
        For Each C In .Range("M3:M" & last_row)
            If C.Value = 0 Then
                If range_to_delete Is Nothing Then
                    Set range_to_delete = .Range("I" & C.Row, "M" & C.Row)
                Else
                    Set range_to_delete = Union(range_to_delete, .Range("I" & C.Row, "M" & C.Row))
                End If
            End If
        Next C
    End With
    
    'Lastly, delete the cells meeting your criteria.
    'Thought this will mess up your data big time since you are deleting row 2 from column I to M
    'so the data in row 3 from column I to M will now be in row 2.
    If Not range_to_delete Is Nothing Then range_to_delete.delete xlUp
    
    'If instead, you just want to clear the cells change:
    'range_to_delete.delete xlUp
    'for:
    'range_to_delete.ClearContents

End Sub