VBA Find Values Below and Delete Row

[syedbokhari]

Hi Guys,

I've attached a screen shot of example piece of data.

I was wondering if someone could help me with piece of work that I'm doing manually.

I took me end-less hours to do manually (data set has 6000+ rows) and my vba writing ability is lacking currently.

Essentially I need a macro which will go into column B look for that value in column A and delete those rows.

Some of the values contain NULLS or BLANKS (although I can just make the NULLS blank cells with a simple find and replace).

The output should look like D1:F8 in the screen shot.

My data set ranges are dynamic so I believe the last row will have to be located.

I will most grateful if someone could point me in the right direction or lend a hand.

Thanks,

Syed

 

[nemmi69]

With B >= to A I get

 

[syedbokhari]

With B >= to A I get

Thanks for the response comrade!

I got the out put slightly wrong .

Column A and B should look like the yellow area (output example).

Essentially 1 becomes almost like a Parent and 2,3,4 are the children.

Currently .... what I have to do manually is find that value in Range B and then go into Range A ... and delete those rows below. Then I repeat the action till I'm left with the yellow box.

Sorry about the mistake in my output above. I greatly appreciate your help.

 

[nemmi69]

Thanks for the response comrade!

I got the out put slightly wrong .

Column A and B should look like the yellow area (output example).

Essentially 1 becomes almost like a Parent and 2,3,4 are the children.

Currently .... what I have to do manually is find that value in Range B and then go into Range A ... and delete those rows below. Then I repeat the action till I'm left with the yellow box.

Sorry about the mistake in my output above. I greatly appreciate your help.

What happened to the 2's in Person ID?

 

[syedbokhari]

The 2's were a mistake before ... they should of not been in the output (yellow part).

1 is the head of the family for 2,3,4. That's why 2,3,4 are not in column D.

6 is head of the family for 7 so that's why there are no 7's in column D.

8 has no other family members so he just stays blank.

9 is head of the family for 10 so that's why there are no 9's in column in D (same case for 6).

All these actions need to occur on Column A and B anyways.

Hope that helped comrade.

Thanks,

Sy

My data set has unique record numbers for these guys ... I just gave small numbers for the example so it's easier to understand.

 

[nemmi69]

Try

Dim Ast As Integer, AEnd As Integer, Bst As Integer, BEnd As Integer


Sub FilterRows()
Ast = 2
AEnd = Sheets("Sheet1").Range("A65536").End(xlUp).Row


Do While Ast <= AEnd
    If Sheets("Sheet1").Range("A" & Ast).Value < Sheets("Sheet1").Range("B" & Ast).Value Then
        Bst = Ast + 1
        BEnd = AEnd
        Do While Bst <= BEnd
            If Sheets(1).Range("A" & Bst).Value = Sheets(1).Range("B" & Ast).Value Then
                 Sheets(1).Range("A" & Bst & ":B" & Bst).Delete xlShiftUp
                 AEnd = AEnd - 1
                 BEnd = BEnd - 1
            Else
                Bst = Bst + 1
            End If
        Loop
    End If
    Ast = Ast + 1
Loop


End Sub

Make sure you have a backup first.

 

[syedbokhari]

I want to say that I am immensely grateful nemmi69!

I can see the piece of code and I can even grasp what's it's doing with my level of knowledge of VBA.

I gave the exact number as I got when I did manually.

If your ever in London let me know ... lunch is on me comrade!

Again thank you!

 

[nemmi69]

Cheers!