Using loop in VBA to delete a range and changing the range every iteration

Question

I want to select a range in Excel (A50:B80) and delete all of its content. I will keep two rows and I want the next loop iteration to then start at (A52:B82) and so on. This is what I have so far:

Sub Macro2() 
 Dim i As Integer
 Dim num As Integer
 num = 50
    
 Dim num_2 As Integer
 num_2 = 80
    
 For i = 1 To 2:
     Set Range1 = Range("A" & num)
     Set Range2 = Range("B" & num_2)
     Range(Range1, Range2).Select
     Selection.delete Shift:=x1Up
     num = num + 2
     num_2 = num + 2
 Next i
End Sub

But I keep getting "Run-time error '1004: Delete method of Range class failed.

Any help would be appreciated!

Don't do that. Use `Union` to construct a range to delete, then delete in one step.. like demonstrated [here](https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba). — BigBen, Oct 30 '20 at 14:46
You must run the loop backwards, or to put all the ranges to be deleted in a Union range and delete it at last. In this way, it deletes the first range and then it looses the reference, because of the previous deletion... — FaneDuru, Oct 30 '20 at 14:47
@FaneDuru I see what you are hinting at but after my first initial range deletion, there will be 2 rows kept. So then if the loop starts again at the first range set, it will delete the two rows I wanted to keep. Or maybe I am misinterpreting what you mean — McMckinson, Oct 30 '20 at 14:58

VBasic2008 · Answer 1 · 2020-10-30T15:53:20.547

Delete a Series of Ranges

How to Avoid Select

You don't want to delete anything until you're not sure what will be deleted. Therefore always use Select while testing the code.

The Code

Option Explicit

Sub QuickFix()
    
    Dim First As Long
    First = 50
    Dim Last As Long
    Last = 80
    Dim i As Long
    
    Dim Range1 As Range
    Dim Range2 As Range
    Dim rng As Range
    Dim dRng As Range
    
    For i = 1 To 2
        Set Range1 = Range("A" & First)
        Set Range2 = Range("B" & Last)
        Set rng = Range(Range1, Range2)
        If Not dRng Is Nothing Then
            Set dRng = Union(dRng, rng)
        Else
            Set dRng = rng
        End If
        First = First + rng.Rows.Count + 2
        Last = Last + rng.Rows.Count + 2
    Next i
    
    If Not dRng Is Nothing Then
        dRng.Select
        Debug.Print dRng.Address
    End If

End Sub

Sub deleteRangesWithOffset()
    
    ' Define constants. All these you can change and see the differences.
    Const FirstRow As Long = 50
    Const LastRow As Long = 80
    Const FirstCol As String = "A"
    Const LastCol As String = "B"
    Const NumOfEmptyRows As Long = 2
    Const NumOfRanges As Long = 2
       
    ' Define Initial Range ('rng').
    Dim rng As Range
    Set rng = Range(FirstCol & FirstRow & ":" & LastCol & LastRow)
    ' or e.g.
    'Set rng = Range(Cells(FirstRow, FirstCol), Cells(LastRow, LastCol))
    ' or just...
    'Set rng = Range("A50:B80")
    
    Dim cRng As Range ' Current Range
    Dim dRng As Range ' Delete Range
    Dim i As Long     ' Current Ranges Counter
    
    For i = 1 To NumOfRanges
        ' Define Current Range.
        Set cRng = rng.Offset((i - 1) * (rng.Rows.Count + NumOfEmptyRows))
        ' This is how you combine (collect) the ranges into 'dRng'.
        If Not dRng Is Nothing Then
            Set dRng = Union(dRng, cRng)
        Else
            Set dRng = cRng
        End If
    Next i
    
    ' Check if there was any range 'collected' (Here it is not necessary).
    If Not dRng Is Nothing Then
        ' Test with select to see what is happening.
        ' Now maybe increase NumOfRanges to 4 and/or NumOfRanges and see
        ' what is selected... etc.
        ' Only later use 'Delete'.
        dRng.Select
        Debug.Print rng.Address ' See the range address in the Immediate window.
    End If
    
End Sub

This one is awesome! Thank you! It is selecting what I want to delete. What should I put in to delete what is selected? Selection.Delete is not working when I put it after dRng.Select — McMckinson, Oct 30 '20 at 17:11
@McMckinson: Instead of `dRng.Select` use `dRng.Delete`. `Select` is just a precaution, because sometimes it is hard to define the range, so you test with `Select`. Otherwise `Select` is to be avoided as you can find out following the link at the beginning of this post. — VBasic2008, Oct 30 '20 at 17:13
Amazing! I see what you mean now. So if I delete it will the cells shift up automatically? Or would I have to add the command in? I apologize for sounding a bit ignorant haha, It's just been a while since I learned VBA and am interning right now and there's a project that this automation would help for. — McMckinson, Oct 30 '20 at 17:25
@McMckinson: Frankly I don't know. But you should create a copy of your project and then try different things like `Shift:=xlUp` (remember it's the letter 'l' in `xlUp`). I usually only delete the complete rows which you could achieve by using `dRng.EntireRow.Delete`. There are so many options in VBA or Excel, that you just can't memorize everything, so you lookup stuff you need at the moment on the internet, just like you did. — VBasic2008, Oct 30 '20 at 17:32

Using loop in VBA to delete a range and changing the range every iteration

1 Answers1

Delete a Series of Ranges