VBA Does not contain

[RoyalAnne]

Dear All,

I am an intern and I've been tasked with figuring out how to delete all rows in a spreadsheet which are not flagged as not important data. In VBA i want to write a code where excel can delete those in important data for me. The cells which are containing n or c must be deleted. How can i do that?

Thank you so much!

[Hawkeye16]

Is the n or c always in the same column of the row? Could you attach a sample workbook with a before and after tab with non sensitive data?

[RoyalAnne]

He! Je bent Nederlands

No they are not. It is a number so the number starts with .../../00231 or ..../../N0231 or .../../C0231. I only need the numbers which are written like the first one. (see row F)example.xlsx

I can make a sample, but i can not add the original document

[RoyalAnne]

Range(F1).Select
Do Until Selection.Value = "xxx"
If Selection.Value = "N" Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Loop

I already wrote this, but i am not sure if it is ok.

[Hawkeye16]

Based on your example I think something like this should work

Please Login or Register  to view this content.

Make sure you use code tags when attaching code, it helps the forum look nicer

This code looks at all columns in column F and finds the values to the right of the last "/" and checks if it is a number or not. If it is not a number (any letter present) it deletes the row.

This does not take into account if there are values without "/" in them, in which this macro will error out. Error handling would be needed for that scenario.

[RoyalAnne]

But it looks like you delete the letters from the 'name' now? Because the intention is the delete the entire row if the name contains a c or a n.

In excel i did: sort, does not contain c and n, ok.
These are the steps i want to have in VBA.

[Hawkeye16]

Mine deletes the entire row if there is a letter present, not just if there is a C or N. It can easily be modified to test just for a C or N.

Please Login or Register  to view this content.

[RoyalAnne]

Thank you so much

[Hawkeye16]

No problem, don't forget to mark the thread as solved and add reputation if you would like!

[JOHN H. DAVIS]

FWIW:

Please Login or Register  to view this content.