VBA Code to delete Row on Content of one Cell

[Jes1397]

Hi All

Trying to learn VBA on the fly as I go. Have a relatively simple task that I thought I had sussed, but apparently not. I have a data range A:O that is collected from other sheets (using a Master and Report Sheet) and pasted into one sheet as values only not the formulas in the Report sheet.

Because it collects a standard length this means there are gaps. In the report sheet I have the formula that states If(ISNOTTEXT) then True returns value Don't Delete and False is Delete. When this is copied into the new sheet as values only the column O has either Delete or Don't Delete. There can be a lot of values in this one sheet (10,000 mostly blank). If I click the row O, go to find, Delete, select all, right click, delete entire row, I get the effect I want. 178 - 200 rows for Columns A:O. However, I wanted to make a button that would do this for me.

This was the code I came up with

Please Login or Register  to view this content.

However, when I click the button my excel goes into a mad frenzy of calculating and processing and eventually crashes. Is it because I have too many entries or am I not being specific enough? It only needs to select all the cells in column O that contain the word Delete only and then delete entire row.

Thanks so much

Jes

[LeoTaxi]

Try like this

Please Login or Register  to view this content.

Kind regards
Leo

[AB33]

You need to loop backwards if you want to delete rows, or use a filter which is easier and faster than looping.

Please Login or Register  to view this content.

[Doc.AElstein]

Hi Jes1397

As AM 33 said, a very important point when you do anything in a “progression” . or Forward looping or “going upwards” ...... You can always get caught out when deleting things.
. You can experiment to death with real code situations, but in simple plain English:

. When I loop with a +ve step or “move forwards” or “go upwards” and then delete something, the next thing I wanted to consider slips down into the “hole” left by the deletion. So when I “move” on I go onto the what is actually the “next next “ and miss out what should have been the next as this thing has slipped into the “hole” at my present position where i deleted.

. Similarly “going forward” and deleting can cause nasty VBA errors, possibly as you have been experiencing, ias VBA may look for the thing to consider in a for each or similar loop type situation.
. This can occur when VBA tries to consider something it thinks should be there as it has not considered it yet. ( VBA sometimes makes an internal memory at the start of a Loop of all things and where they are, and , at least partially, ignores attempts by you to change variable associated with the Loop Control. If then in the Loop you purposely or unintentionally change variables, by, for example, "going out of step” in a forward Loop, you can confuse VBA if it looks for something being sure it should be there but it isn’t ( anymore ) !!

. Going or Looping “backwards” means the bits that are “behind me going upwards” slip down and fill in the hole I just made. All these bits have been considered already. The next thing i then consider going backwards is the true next thing i wanted to consider.
.
. Hope that somewhat layman explanation helps

[AB33]

Alan,
You quite often write long sentences, but this simple jargon-free explanation caught my eyes. You have done a great job.

[Doc.AElstein]

Hi

Alan,
You quite often write long sentences, but this simple jargon-free explanation caught my eyes. ......

Thanks...
ironic – the simplest are often the best. I was feeling a bit too tired to write my usual “book”... with examples..
Alan.

[goss]

Jes,

Consider using AutoFilter instead of looping by cell. Once you filter the Range you can delete the entire visible Range in one go.

Please Login or Register  to view this content.

[stnkynts]

Please Login or Register  to view this content.

I think he needs column O. Good catch on the autofilter though.

[goss]

Thanks stnkynts,

Change Field:=2 to the correct Field number. If "O", then Field:=15

Even better would be to use an InputBox to prompt the user for some Text, search for the Text, and return the Field the Field Number. This would make the code more dynamic.
Same goes for Criteria instead of hard-coding a value.

[goss]

Thanks stnkynts,

Change Field:=2 to the correct Field number. If "O", then Field:=15

Even better would be to use an InputBox to prompt the user for some Text, search for the Text, and return the Field the Field Number. This would make the code more dynamic.
Same goes for Criteria instead of hard-coding a value.

[skywriter]

You have already created a range rng from the current region.
You don't need to create another range and you don't need special cells.
Since you created rng from the current region, this is all you need.

Please Login or Register  to view this content.