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.
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
