How to delete multiple rows based off a value on another tab?

[mitchinphx]

I have attached a sample to make life easier and thank you in advance. I did look through the help and found lots of ways to delete rows but none referencing a value on another tab that changes. Most were based on static values, but i digress..

I have a list of properties on a tab called "PropList" that have a unique number value in column A. I want to create a macro to delete all of the rows with values in column A that don't match the value that the user enters on the "Info" tab in cell "A4". But I don't want to ever delete the first 4 rows of data as it contains other information. The real report has thousands of rows and tens of columns of data and this will help prune and save file size.

Thank you!!

[rylo]

Hi

Here goes.

Please Login or Register  to view this content.

rylo

[Leith Ross]

Hello mitchinphx,

With that much data to check, you may want a faster macro. This macro first sorts the data using the values in column "A" and filters the column using the value from "A4" on the "Info" sheet. The filtered rows, if there are any, will then be deleted.

Please Login or Register  to view this content.

[mitchinphx]

Thank you! But with that working I discovered a problem. The number on the info sheet displays as 03002 because of formatting, but it is actually read as 3002. So when the filter reads 3002 and doesn't find it in the prop list because there it is 03002 it deletes everything. Can a ltrim function where a 0 is added to the left if it is only 4 numbers be added somehow? There are property numbers that are 10000, 10001, etc. so i don't always need to add a 0.

Thanks again,
mitchinphx

[Leith Ross]

Hello mitchinphx,

I have another way of doing this without using a filter that is still very fast. I am heading off to bed, but will pick this up in the morning.

[mitchinphx]

thank you!

[mitchinphx]

any more thoughts / assistance? i am still stuck... thanks again.