VBA loop or repeat tasks

Resolved · Medium Priority · Version 2016

Tue 12 Mar 2019, 12:35 Delegate Quek said...

Quek has attended:
Excel VBA Advanced course

VBA loop or repeat tasks

Dear team,

How do i repeat this task until there is no more texts "N/A" in the column?

rrow = Sheets("Sheet 2").Range("A" & Rows.Count).End(xlUp).Row

For r = 1 To rrow
If Cells(r, "G").Text = "#N/A" Then
Sheets("Sheet 2").Rows(r).EntireRow.Delete
End If
Next

Somehow, when i run the macros once not all the rows get deleted?
And if i run it a few times then i get the data that i want.

Hope you can help.

Thanks.

Tue 12 Mar 2019, 18:40 Trainer Jens said...

RE: VBA loop or repeat tasks

Hi Shi Lin,

Thank you for the forum question.

If you start from the top you have the problem, because every time you delete a row all rows will move up and you will skip testing a row.

Try:

rrow = Sheets("Sheet 2").Range("A" & Rows.Count).End(xlUp).Row

For r = rrow To 1 step -1
If Cells(r, "G").Text = "#N/A" Then
Sheets("Sheet 2").Rows(r).EntireRow.Delete
End If
Next

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 19 Mar 2019: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Generating Random Numbers

To generate a random number in Excel use the = RAND() function.

The value returned will always be between 0 and 1. To convert this to some other random value, you will need to multiply the result by the highest number you want to consider. For example, if you wanted a random number between 1 and 25, you could use the following code line:
= INT(25 * RAND()+ 1)

Since RAND() will always returns a value between 0 and 1 (but never 1 itself), multiplying what it returns by 25 and then using the Integer function INT on that result will return a whole number between 0 and 24.

Finally, 1 is added to this result, so that x will be equal to a number between 1 and 25, inclusive

View all Excel hints and tips