Using a cell in a loop to define a range in vba. I want to basically delete the row of that cell and-CodePudding

I'm basically writing a clean up program to make it more straight forward to access data. Anywho, I ran into possibly a nomenclature error. I want to use the "current" cell in a "for" loop to delete that row and the next 3 rows. Code looks something like this:

For Each SingleCell In SingleSheet1.Range("a1:a40")
        
            If SingleCell.Value = "S" Or SingleCell.Value = "B" Then
                Range(SingleCell.Range, SingleCell.Range.Offset(4, 0)).EntireRow.Delete Shift:=xlUp
            Else
            End If
            
        Next

I tried to define the range to delete as specified in the code but it gave me a runtime error

CodePudding user response：

Delete backwards looping trough row number:

Sub EXAMPLE_1()

Dim i As Long

For i = 40 To 1 Step 1
    If Range("A" & i).Value = "S" Or Range("A" & i).Value = "B" Then Range("A" & i & ":A" & i   3).EntireRow.Delete Shift:=xlUp
Next i


End Sub


Sub EXAMPLE_2()
Dim i As Long
Dim LR As Long 'in case last row is not always number 40, adapt it dinamically

LR = Range("A" & Rows.Count).End(xlUp).Row

For i = LR To 1 Step 1
    If Range("A" & i).Value = "S" Or Range("A" & i).Value = "B" Then Range("A" & i & ":A" & i   3).EntireRow.Delete Shift:=xlUp
Next i


End Sub

CodePudding user response：

Your code looses the reference for the deleted rows and you should iterate backwards, if you like iteration between cells (which is slow), but a better/faster solution will be to build a Union range and delete all rows at the code end, at once:

Sub testDeleteOffset()
  Dim sh As Worksheet, Urng As Range, i As Long
  
  Set sh = ActiveSheet
  For i = 1 To 40
        If sh.Range("A" & i).Value = "S" Or sh.Range("A" & i).Value = "B" Then
            addToRange Urng, sh.Range("A" & i, "A" & i   3)
            i = i   4
        End If
  Next i
  If Not Urng Is Nothing Then Urng.EntireRow.Delete xlUp
End Sub

If the involved range is huge, a better solution will be to place some markers for the necessary rows (after last existing column), sort on that marker column and delete the (consecutive marked) rows. Another column with the initial order would be necessary to re-sort according to it at the end... The idea is that building a Union range having more than 1000 areas may become slow.