Run / loop through cells and delete based on criteria

Hi all,

Hope all is well.

Hoping someone can help / advise / improve my approach to an issue that does not seem to be working!

Im trying to run (or loop if someone can advise) through a column of cells, and if cell contains certain text (e.g. ALPHA, BRAVO, CHARLIE) and delete the row if found.

My macro as I run it:

Sub DATA_TEXT ()

'Number of cells to loop through (I am unsure how the .xlend works!)
For i = 1 to 2500

'If function defining the criteria.
If cells (1, i) = "ALPHA" OR "BRAVO" OR "CHARLIE" Then

'Delete the entire row if match found.
Cells(1,i).Entirerow.Delete

Else

End if

Next i

End sub


Any suggestions, both in this specific case or how to better approach something like this would be greatly received! Eager to learn :D


Many thanks,


Coeus.

Hi,

Could you please use code tags in future (as stated in the Forum Rules), and also indent your code as it makes it much easier to read.

This code should fix your problem:

Please Login or Register  to view this content.

I hope this helps

Hi adryan88.

Sorry for the late reply murder at work.

THANK YOU VERY MUCH!

It worked like a charm and apologies for not using the code tags. Will re-visit how to do it!

Could you explain a few statements for my understanding? Hate to just copy!

xlUp (and I suppose xlDown) - what does this actually mean?

The 'lastRow to 1 step -1' - this seems to be running from last row to the beginning. If I have this right what does the 'Step -1' bit mean?

Do you have to specify '.Value' if inputting a text term (i.e. rather than Cells(1,1) = "ALPHA")?


Again many thanks for any replies greatly appreciated.


Regards,

Coeus.

No worries. You're welcome

The xlUp statement will find the last used row in column "A" when used in the context as I have above. It is equivalent to clicking on the absolute last cell in column A, holding Ctrl, and pressing the Up arrow. xlDown would work differently in this case. If you have data in cells A1, A4, A7 and A12, xlUp will return 12, but xlDown will return a value that is dependent upon which cell is currently selected. If cell A1 was selected, and you pressed Ctrl + Down, it would select cell A4, then A7 next time you press it, then A12. So I guess in summary, xlUp finds the last used row in a column (search from the bottom up), and xlDown finds the start of a contiguous range in a column (searching from the top down).

The Step -1 part is what tells the code to loop backwards, decrementing one row at a time. This is required because VBA isn't smart enough to recognise that if lastRow is greater than 1, then we obviously want to loop backwards. If unspecified, it automatically assumes the equivalent of using a "Step 1" statement - increment in the positive direction, by a value of 1 each loop.

I don't actually know, as I've never tested it, but I think you do. If using the Range("A1") statement, I believe you don't (as long as the range is only a single cell), but I think when using the Cells(1, "A") (or Cells(1, 1)) statement, it is necessary.

Please don't forget to mark this thread as solved

Ahhh very well explained thank you very much.

Will have the confidence to incorporate into my future macros.

Very sad but I love these macros! Made me deadly efficient :D


Your a star!