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.

 

[diddi]

hi and welcome to the board

if you are only doing this once,

you can sort by col A, all the values will group so that all the "05"'s will be togeteher. then you can copy/paste.

otherwise vba needed

 

[MichaelRM]

Well that sounds like a great idea and I actually did it and it took me a little while before I realized there was something wrong, lol. I need the information to stay in the same order. That is why I can't do it that way. I need some VBA code to help me out.

Thank you for your try to help though.

 

[RSXchin]

Try this in a copy

Sub Delete_all_others()
    Dim Lastrow As Long
    Dim FirstTwo As Double
    Dim i As Long
    Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    FirstTwo = InputBox("What do you want to KEEP?")
    For i = Lastrow To 2 Step -1
        If Left(Cells(i, 1), 2) <> FirstTwo Then
            Rows(i).EntireRow.Delete
        End If
    Next i
End Sub

 

[Peter_SSs]

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?

 

[MichaelRM]

Try this in a copy

Sub Delete_all_others()
    Dim Lastrow As Long
    Dim FirstTwo As Double
    Dim i As Long
    Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    FirstTwo = InputBox("What do you want to KEEP?")
    For i = Lastrow To 2 Step -1
        If Left(Cells(i, 1), 2) <> FirstTwo Then
            Rows(i).EntireRow.Delete
        End If
    Next i
End Sub

Great work, THANK YOU!!!!! That did the job but if at all possible can you write it where it does 1 major delete rather than a bunch of individual deletes? If not, no problem. It's more of just a preference than anything.

 

[Peter_SSs]

Try this in a copy

Sub Delete_all_others()
    Dim Lastrow As Long
    Dim FirstTwo As Double
    Dim i As Long
    Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    FirstTwo = InputBox("What do you want to KEEP?")
    For i = Lastrow To 2 Step -1
        If Left(Cells(i, 1), 2) <> FirstTwo Then
            Rows(i).EntireRow.Delete
        End If
    Next i
End Sub

You would need to be careful in using that. If the user wanted to keep '05' but accidentally typed '054' they would have distastrous results.

 

[RSXchin]

whoops....
add these lines

Sub Delete_all_others()
     application.screenupdating = false
.
.
.
.
.
.
.
     application.screenupdating = true
End Sub

 

[Peter_SSs]

Sorry, I didn't really say what I was trying to say with my last post. The user is asked what they want to keep. If they want to keep '054' and enter that in the Input box, that's not the result they will get.

 

[MichaelRM]

Sorry, I didn't really say what I was trying to say with my last post. The user is asked what they want to keep. If they want to keep '054' and enter that in the Input box, that's not the result they will get.

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.