Need a little help with a macro.

[MichaelRM]

<hr style="color:#ebebeb; background-color:#ebebeb" size="1"> I need a macro to delete all rows that do not contain a certain value. The value that needs to be looked at will always be in column A. For example:

ColA: 0541073600000040320000, ColB: 95.4' X 401.3' IRR LOT 29 GREEN OAKS
ColA: 0541093000000130020000, ColB: 50'(S) X 20'(S) IRR COM AT NE COR OF

So what will need to be looked at every time is the first two digits in ColA. I need a popup box to ask me for the value because it can be a number of different values. But it will always be the same two digits for the particular spread sheet.

Ok so for example I will implement a button on any spread sheet I create and when I click it I need it to ask me what two digits need to be looked for. In this case I would put 05 and then it would go through and delete every row where ColA does not start with 05.

On a different spread sheet the digits may be 02 for example. If anyone could help me with this it would be very much appreciated.

Also I would like to have the macro just make one major delete instead of a bunch of individual deletes. If you need to see the spread sheet please let me know and I will zip it up and give it to you.

Thank you.

 

[Peter_SSs]

To tell you the truth you may be able to help me do what I am doing a whole lot easier with probably one macro where I am using two and changing code in one back and forth as well as still having to copy and paste stuff.

Before we change the goalposts, lets see if we have solved what we started with.
How did my code go?

If you have the time and wouldn't mind I can shoot you an email of 2 excels. One the way I need all of the info and another the way I have to start out.

I'm sorry but many helpers here, including me, choose not to receive files from other members or download from other sites. There are a number of reasons including ..

- Having done so in the past it often turns out that the workbook/worksheet is quite complex and takes a lot of time to become familiar with. (If so, it may mean the problem is really too complex for a free public forum like this.)

- It tends to take the onus off the 'asker' to distil their problem to something clear and concise and becomes more "Here's my workbook, can you figure out what I'm trying to do and fix it?"

- It defeats the purpose of the forum being a public forum.

We also need to be mindful of #7 of the Forum Rules and points 18-19 of the Posting Guidelines

 

[MichaelRM]

Before we change the goalposts, lets see if we have solved what we started with.
How did my code go?

I'm sorry but many helpers here, including me, choose not to receive files from other members or download from other sites. There are a number of reasons including ..

- Having done so in the past it often turns out that the workbook/worksheet is quite complex and takes a lot of time to become familiar with. (If so, it may mean the problem is really too complex for a free public forum like this.)

- It tends to take the onus off the 'asker' to distil their problem to something clear and concise and becomes more "Here's my workbook, can you figure out what I'm trying to do and fix it?"

- It defeats the purpose of the forum being a public forum.

We also need to be mindful of #7 of the Forum Rules and points 18-19 of the Posting Guidelines

I can definitely understand all of that. Plus it will be more beneficial to me to do it myself anyways. However, when it comes to your code. It works, although, I run into another problem, it seems it is just clearing the cells and not deleting them, Which in turn is messing up the format of the numbers on some of the cells.

For example instead of the number format being:055404202000003100 it turns out being something like 5.54042E+16

 

[Peter_SSs]

However, when it comes to your code. It works, although, I run into another problem, it seems it is just clearing the cells and not deleting them, Which in turn is messing up the format of the numbers on some of the cells.

For example instead of the number format being:055404202000003100 it turns out being something like 5.54042E+16

I had assumed that column A was formatted as 'Text' otherwise I thought those long 'numbers' would already have converted to values like 5.54042E+16.

Does it help if you add the blue line of code where shown?

With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 3)
    .Columns(1).NumberFormat = "@"
    a = .Value