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

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

@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 ;)
@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

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

2 Comments

Related