CODEWITHSUNDEEP
vbaexcel
Christian Duran
Christian Duran

Reputation: 17

Dynamic Range with For Each Loop

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.

Upvotes: 0

Views: 2009

Answers (1)

Tom
Tom

Reputation: 9878

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

Upvotes: 2

Related Questions