Dynamic Range with For Each Loop

Question

I'm trying to figure out a dynamic range that will select a range starting from the active cell in a for each loop. For instance, if cell A2 is selected in my for each cell loop, the range in the loop being A2:A20, and it contains "IP," it will select the range A2:M2, delete the contents, and shift all the values below, A3:M20, up to fill the emptied cells.

Sub deletewirelessdevice()

Dim rng As Range
Dim wksSource As Worksheet


Set wksSource = ActiveWorkbook.Sheets("dt-attext")

Set rng = wksSource.Range("A2:A500")

For Each Cell In rng
    If InStr(1, ActiveSheet.Range(ActiveCell).Value, "IP") > 0 Then
        Range(ActiveCell, "M" & ActiveCell.Row).Select.Delete Shift:=xlUp
Next Cell

End Sub

I'm not sure if there is a mistake in the selection and deletion as I can't get the code to run due to a Next without for compile error. There is a matching for so I don't know what the problem is. Any advice is welcome.

You're missing the End If. Also you don't change anything in each loop iteration. Replace ActiveCell with Cell where appropriate — Tom
A missing End If is causing your error. But Range(ActiveCell) will only work if the activecell contains a cell address. — SJR
You can replace ActiveSheet.Range(ActiveCell).Value with ActiveCell.Value but I suspect what you actually want is Cell.Value2 — Tom
Agree with @Tom. Also you should loop backwards when deleting cells otherwise you will skip rows. — SJR
@SJR and Tom thank you! Tom's code worked perfectly but I'm definitely gonna look over these for some pointers. — Christian Duran

Tom Tom · Accepted Answer · 2017-11-20T11:50:58

You had a number of issues with your code so I've tweaked it and inferred what you intended. This should work, however do read the comments above as well for some pointers on how to handle it next time

Public Sub deletewirelessdevice()
    Dim DelRng As Range
    Dim ColOffset As Long

    With ActiveWorkbook.Sheets("dt-attext")
        ColOffset = Range("M" & 1).Column - 1
        For Each cell In .Range("A2:A500")
            If InStr(cell.Value2, "IP") Then
                If DelRng Is Nothing Then
                    Set DelRng = Range(cell, cell.Offset(0, ColOffset))
                Else
                    Set DelRng = Union(DelRng, Range(cell, cell.Offset(0, ColOffset)))
                End If
            End If
        Next cell

        If Not DelRng Is Nothing Then DelRng.Delete Shift:=xlUp
    End With
End Sub

Dynamic Range with For Each Loop

1 Answers