Rows.Delete not deleting first row in range

Wouldn't have thought so. Nothing to do with the looped range. You could use Or instead of ElseIf.

More likely the data isn’t what you think it is. Can't help there without seeing the file.

Hi,

Maybe because the 9th iteration of the loop isn't processed since r is incremented at the last but one NEXT instruction.

However looping just isn't the best way of doing this sort of thing.

Whenever you find yourself creating looping code stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop.

The fastest, most efficient and elegant way for this sort of stuff is to use an Autofilter to filter the rows you're interested in.

Please Login or Register  to view this content.

This uses just one Delete instruction rather than many if you're looping through many cells.

Make use of formula in helper column and delete all matching at once with autofilter...

Originally Posted by hrlngrv

Quibble: AutoFilter requires having a column on which to apply it. You should have been explicit about that.

The code I offered did necessarily show the field on which to filter. It would have debugged otherwise.
When you say being 'explicit' what did you have in mind?

The essential point was to make the point that loops should be the last resort for stuff like this

While there have been several alternative ways offered of achieving the intended goal, that is really not the point. Without seeing the file, it is impossible to guess at the reason that row 9 is not deleted IF, as stated, it meets the criteria.

There is nothing wrong with your code. It is, therefore the data. I suspect that, instead of "True ", you have "TRUE" without the trailing space. Whatever, that cannot be proven without the workbook.

In the absence of that workbook and, as a sign of frustration, I have created one and tested the code, and some variations. It is attached.

The code variations:

Please Login or Register  to view this content.

Updated version incorporating Richard Buttrey's solution, reflecting hrlngrv's comments.

Please Login or Register  to view this content.

You have a leading space on the text in row 9, column I (cell I9)

Not that it matters, but your code is password protected so we can't see your code.

Your code tests for "Till Difference CASH ", that is, with a trailing space. If you remove trailing spaces, your code will not work ... unless you also remove it from your test in the code.

But as was mentioned earlier this coding causes it to run slow so is there another way to do it.

You have been given at least three options ... and I have summarised and tested them. At least two of them use a single delete. I have taken the suggestions from Richard Buttrey and hrlngrv and refined and tested them. They both use only a single delete, although Richard's AutoFilter suggestion will require a Helper column as described by hrlngrv.

The second example workbook has ALL the code variations. Try them and see which works best for you.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

This should delete all columns E:K and shiftup when...

So not delete all rows...

But as was mentioned earlier this coding causes it to run slow so is there another way to do it.

Making use of sample file supplied in post 14 and suggested helper column with formula

Please Login or Register  to view this content.