Collum Delete Macro Needs to run twice to do it's job!?

[exarranum]

I'm using a macro to delete all rows where the value in column K is 1 or blank, but leave if it's 0.

Please Login or Register  to view this content.

On the first run it will delete the rows where cell value is 1 but leaves all rows where cell value is blank. On the second run it takes care of blank ones too.
Note, even if there are no 1 values the macro still needs to run twice to get the job done. Messy solution is obviously to just write it twice, but i'd like to know whats going on.
Any thoughts?
Cheers

TLDR

I see the confusion-the rows are being deleted but the used range is not reset. add

Please Login or Register  to view this content.

as the last line of code

[Trebor76]

On the first run it will delete the rows where cell value is 1 but leaves all rows where cell value is blank. On the second run it takes care of blank ones too.

Correct. As with any nested If statement, each component is tested and if True is dealt with (actioned) and then the If statement is closed, so...

Please Login or Register  to view this content.

...deletes all the zeros and does nothing else. The next time the code is run it skips that part of the nested If statement as all the zeros have now been deleted and so evaluates the next nested section.

To do it all in one statement you need to use the Or function like so (though on a copy of your data in case the results are not as expected):

Please Login or Register  to view this content.

HTH

Robert

[exarranum]

Thanks for the suggestion but I don't quite unterstand. Even if there are no cells greater than 0 it still needs to be run twice. Also it's a loop so it is ment to stop and restart after it finds a cell with 1 or nothing (deletes the row and moves to the next) or a cell with 0 (moves down 1 row)

Your macro didn't delete the empty rows untill

Please Login or Register  to view this content.

was changed to

Please Login or Register  to view this content.

and even then it still had to be run twice.

Really wierd.

(I'm checking for empty rows by using Ctrl End, first time around the number of rows are the same, secont time round any rows without data are gone)

[Trebor76]

Also it's a loop so it is ment to stop and restart after it finds a cell with 1 or nothing (deletes the row and moves to the next) or a cell with 0 (moves down 1 row)

The rules of a nested If that I explained still apply to a loop. If the cell value is greater than 1 the row is deleted and the code then moves to the next row as that part of the If statement has been met.

Your macro didn't delete the empty rows untill

Cells(Rows.Count, "K").End(xlUp).Rowwas changed to

ActiveSheet.UsedRange.Rows.Count

That's odd as isn't the data being checked if it's to be deleted or in column K?

Perhaps you could attach a sample workbook with before and after scenarios of how the data will look before and after the desired macro is run?

Robert

[JosephP]

if there are blank cells at the bottom of column K in the used range using end(xlup) on that column will ignore them ;-)

there should be no need to loop twice though with your change to loop backwards

[Trebor76]

if there are blank cells at the bottom of column K in the used range using end(xlup) on that column will ignore them ;-)

Correct, I meant blanks i.e. "" not empty cells. Sorry my bad.

Robert

[exarranum]

Here's an example. It's not the original but replicates the issue more clearly.
By stepping through the code I can see where it's going wrong.
First round it will cycle through the rows deleting DATA where appropriate.
Second round it will cycle through the rows deleting ROWS where appropriate.

Something about the EntireRow.Delete will delete data from a row but not actually delete the row unless there is no data to begin with.

Try deleting all the "a" values from cells with "" in the k column. The macro should work first time as it's deleting the rows as there is no data to deal with first.

Not a huge issue, but an interesting oddity I'd like to understand.
Thanks again!

[Trebor76]

I think this is what you're after:

Please Login or Register  to view this content.

The code also goes into a standard module, not on the worksheet.

Regards,

Robert

[exarranum]

I'm afraid not. Hit Ctrl End after running. You can see the data is gone but the rows are still there.
I'm convinced it's delete row function that's the issue. Please note I am simply running the original code twice which is getting the job done just fine. Persuing this is pretty much out of curiosity.

[JosephP]

I see the confusion-the rows are being deleted but the used range is not reset. add

Please Login or Register  to view this content.

as the last line of code

[Trebor76]

Thanks JP. As Dave Hawley says here, the use of usedrange can be unreliable.

[exarranum]

Ahh, works now. I still don't fully understand (how come the used range IS reset if there is no data in the rows?) but this is certainly the answer.
Thanks very much!