Excel VBA - variable for loop

Question

I can't seem to get my second for loop right. I'm looking for the cell with value 'Persoonlijke prijslijst'. Once I have this cell I need to go up two and delete 8 down. When I debug, it says temp = 0 so I presume it's in my second for loop.

Dim i As Integer
For i = 1 To 800
    Range("C" & i).Select
    If Range("C" & i).Value = "Persoonlijke prijslijst" Then
        Dim temp As Integer
        For temp = i - 2 To temp + 8
            Range("C" & temp).EntireRow.Delete Shift:=xlToLeft
        Next temp
    End If
Next i

Do you mean go up two rows and then delete 8 rows down from there? — Siddharth Rout
– Siddharth Rout, Commented Mar 14, 2012 at 15:03
It's because you're deleting rows within that second for loop. Every time a row is deleted, the temp variable would also need to be updated. But it's better to do it Kyle's way. — mattboy
– mattboy, Commented Mar 14, 2012 at 15:27

SWa · Accepted Answer · 2012-03-14 15:35:17Z

5

Another way without looping 800 times:

 Sub testing()

Dim rng As Range
Dim fAddress As String
Dim rngRows As Range

With Sheet1.Range("C1:C800")
    Set rng = .Find("Persoonlijke prijslijst")
    If Not rng Is Nothing Then
        fAddress = rng.Address
        Do
        If rngRows Is Nothing Then
            Set rngRows = rows(rng.Row - 2 & ":" & rng.Row + 5)
        Else
            Set rngRows = Union(rngRows, rows(rng.Row - 2 & ":" & rng.Row + 5))
        End If
           Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing And rng.Address <> fAddress
    End If
End With

rngRows.EntireRow.Delete

End Sub

edited Mar 14, 2012 at 15:35

answered Mar 14, 2012 at 15:13

SWa

4,36325 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

CustomX Over a year ago

Does this work for multiple selections? So if Persoonlijke prijslijst is in my document 4 times?

Siddharth Rout Over a year ago

@Tom, I have covered about .Find and .FindNext in this link. "siddharthrout.wordpress.com/2011/07/14/…" For such small number of rows, you can use looping. However if you use .Find you have to be very careful as if you delete the range then Set rng = .FindNext(rng) line will give you the error ;) Let me know if you want a .Find code as well :)

CustomX Over a year ago

@SiddharthRout, so using .find and .findnext is less CPU intensive? I haven't used this function in the past, so I'm quite interested. If you have time and don't mind, I would say yes to a .find version :)

SWa Over a year ago

@SiddharthRout is quite right, I hadn't tested it. I've updated my original code :S Thanks Sid

SWa Over a year ago

@Tom, Find is fast, but it also requires less looping, you should try and avoid looping in VBA since it is slow. Calling the worksheet (doing things with it) is also slow so should be done as little as possible or all in one go if you can

|

Siddharth Rout · Accepted Answer · 2012-03-14 15:11:53Z

4

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim StrSearch As String
    Dim i As Long

    '~~> Change this to the relevant sheet name        
    Set ws = Sheets("Sheet1")

    StrSearch = "Persoonlijke prijslijst"

    With ws
        For i = 800 To 1 Step -1
            If .Range("C" & i).Value = StrSearch Then
                .Rows(i - 2 & ":" & i + 5).Delete
            End If
        Next i
    End With
End Sub

answered Mar 14, 2012 at 15:11

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

2 Comments

Siddharth Rout Over a year ago

@mattboy: It's not about points. I never care for that ;) So you may reverse the vote that you gave. It about doing something right ;)

mattboy Over a year ago

@Siddhart: Nah you still deserve it. Yours probably actually works!

Collectives™ on Stack Overflow

Excel VBA - variable for loop

2 Answers 2

12 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related