IF/Then using an external text file macro

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Is there anyway to write a macro that will scan an external text file for possible column values?

I have very large datasets that I have to "clean" to only include address data from the United States. One of my columns contains only state data and I would like to write a macro that will delete any row that does not have a state (as defined in my external text file) in that specific column.

Basically,

IF Column "A" <> "states.txt"
Then .deleteRow

Please forgive me as I am new to writing macros and VBA. If you could help, that would be wonderful.

Thank you

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
from what I understood from your question, you want to delete a row if the value in column A does not exists in your text file

For that you can either import that text file in excel and then do the manipulation or
you should read the file and store the values in memory and then process it.

Depending how many rows that text file has, but importing into excel and then can create a temp column to either do a look up or a match between imported data and column A and then delete any row that did not had a match