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]

As I may never run into the problem you are speaking of it really doesn't pertain to this particular problem I had, however, I am trying to learn VBA from examples and tut's I was wondering if you could please elaborate on the problem? It could also quite probably help someone else looking for something similar.

If you could please say why we would have a problem and what exactly causes such a problem that would be great!

Thanks in advance.

The problem is this. The input box simply asks you what you want to keep. Suppose I decide I want to keep data starting with 054 so I type that into the InputBox. 054 would be stored in the variable 'FirstTwo'. Now in the code there is a section

If Left(Cells(i, 1), 2) <> FirstTwo Then
    Rows(i).EntireRow.Delete

Given that my 'FirstTwo' is actually 3 characters long it will never be equal to the left 2 characters of any value in column A so every row of data will get deleted.

So, that's an answers to your questions, what about my questions from post #5?

 

[Peter_SSs]

BTW, the code will also error if you get to the Input and decide you don't want to proceed and click, Cancel or OK (with nothing - or text - in the InputBox) or try to close the InputBox with the X at the top right.

 

[MichaelRM]

Welcome to the MrExcel board!

Roughly how many rows of data in the original list?

Is there a heading row?

Are there rows among the data where column A is blank?

What version of Excel are you using?

Edit: Are there only 2 columns of data?

Oh, Peter, I am sorry about that. I have been trying to figure something out to work the majority of the day and I guess I was so excited when I saw there was a solution I just hit quote and then never even saw your #5 post, lol And for the answers.

Rows of data: 14,000 - 16,000. I would like to have the rows be a variable because I will never know how many rows there will be. And no there is not a heading row. Yes there will potentially be blank cells in column A some times. I am using 2007. And yes I noticed the cancel button problem. Already managed doing that, lol. How would I go about fixing that?

 

[Peter_SSs]

There was one further question:

Edit: Are there only 2 columns of data?

If more, how many?

 

[MichaelRM]

There was one further question:If more, how many?

Sorry about that, I guess my eyes are failing me at this point, lol. Anyways there are 3 columns to begin with after this process takes place there will be 2.

 

[Peter_SSs]

Sorry about that, I guess my eyes are failing me at this point, lol. Anyways there are 3 columns to begin with after this process takes place there will be 2.

How will the columns reduce from 3 to 2 after this process? I thought the process was just about deleting rows, not columns.

 

[MichaelRM]

How will the columns reduce from 3 to 2 after this process? I thought the process was just about deleting rows, not columns.

Well it will start out with 3 columns because when everything first gets put in some of the rows have 3 columns but after this process takes place the rows with 3 columns will all be gone.

 

[Peter_SSs]

OK, test this on a copy of your workbook.
Also probably best to start testing with a lot less rows.
It should be considerably faster than the previous code.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DeleteUnwanted()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LC <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, L <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Keep <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    Keep = InputBox("Starting characters to keep")<br>    L = Len(Keep)<br>    <SPAN style="color:#00007F">If</SPAN> L > 0 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 3)<br>            a = .Value<br>            LR = <SPAN style="color:#00007F">UBound</SPAN>(a, 1)<br>            LC = <SPAN style="color:#00007F">UBound</SPAN>(a, 2)<br>            <SPAN style="color:#00007F">ReDim</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> LR, 1 <SPAN style="color:#00007F">To</SPAN> LC)<br>            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR<br>                <SPAN style="color:#00007F">If</SPAN> Left(a(i, 1), L) = Keep <SPAN style="color:#00007F">Then</SPAN><br>                    k = k + 1<br>                    <SPAN style="color:#00007F">For</SPAN> j = 1 <SPAN style="color:#00007F">To</SPAN> LC<br>                        b(k, j) = a(i, j)<br>                    <SPAN style="color:#00007F">Next</SPAN> j<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> i<br>            .Value = b<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[MichaelRM]

How will the columns reduce from 3 to 2 after this process? I thought the process was just about deleting rows, not columns.

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.

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.

 

[MichaelRM]

Actually it would be 3 excels because I have two I have to get everything off of and then one I paste it all on in a specific order.