Delete Row Based on Row Position

[syedbokhari]

Hi Guys,

I have the row number of some values in a sheet (column E) see picture.

I need to delete the row where the corresponding value sits.

I have used match to give me this as you can see from the picture.

The issue is that the value sometimes can be present in the look up column (column A).

Upon deleting the ROW the match will provide an NA which is perfectly fine as this will indicate there is only one unique value in column A.

Currently the value in column E can also be in A ...

That will indicate that this is the only value present and the corresponding value isn't present anymore.

The final result should look like this.

The data set is much larger then this so I have just shortened it to make it easier (+6000 Rows).

I tried writing this in VBA but my skills aren't up to scratch as I'm still a beginner.

If you guys could help that would be awesome and I would be most grateful. Unfortunately I can't send food across the internet otherwise I would of provide you guys with some lemon cake.

Thanks,

Sy

 

[ravishankar]

Try the following codes. I don't have data to test it though. try it on a co[y of your file (and not original)

Sub lemon_cake()
Dim a As Integer, b As Integer, x As Integer
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = x To 1 Step -1
b = Application.WorksheetFunction.Match(Range("C" & a), Range("A1:A" & x), 0)
If b > 0 Then
Rows(a).Delete
End If
Next a
MsgBox "complete"
End Sub

If it works for you, I will ask my nephew to drop in next month in London.

 

[syedbokhari]

Try the following codes. I don't have data to test it though. try it on a co[y of your file (and not original)

If it works for you, I will ask my nephew to drop in next month in London.

Thank you for replying Ravishankar!

Unfortunately it didn't work as it gave a debug error.

The line of code with b= was flagged as yellow.

Tell him to come I will take him to lunch on me regardless of it working or not.

I live in South West London and live close to the centre.

Sy

 

[pizzaboy]

Before running the Delete call... have you sorted by the "Delete row" descending?

If you delete a row with a value less than another one which needs to be evaluated later, everything less would have to subtracted by 1 otherwise...

For instance, if you remove row=1, the "Delete row=2" would become the new row=1... if that makes sense....

Sorting by the "delete row" descending, would mean you wouldn't have to take that into consideration.

 

[syedbokhari]

Before running the Delete call... have you sorted by the "Delete row" descending?

If you delete a row with a value less than another one which needs to be evaluated later, everything less would have to subtracted by 1 otherwise...

For instance, if you remove row=1, the "Delete row=2" would become the new row=1... if that makes sense....

Sorting by the "delete row" descending, would mean you wouldn't have to take that into consideration.

Hey Pizza boy!

Unfortunately that row has formula in providing that value ... I did try but as there is a formula there it won't descended them properly.
If I paste them as values then I don't think it will as it will delete the incorrect rows because the rows will change when the delete call is undertaken.

Maybe I'm doing the impossible hmmmm

 

[pizzaboy]

With your #NA value, try excel's =IFERROR(YourFunction(),"")

Its really hard for you to code and evaluate unexpected values...

Then you can check in VBA/Excel =IF(value<>"",... ,...)

Might not solve your problem, but at least makes it easier for you to see what the problem is...

 

[pizzaboy]

Wow... your dilemma is a bit of a headache....

Try this...

With your data, put it inside an ACTUAL excel table (Format as Table)...

For your MATCH function "=IF(IFERROR(MATCH([RELATIVE'S ID],[PERSON ID],0),0)>0,MAX([PERSON ID],[RELATIVE'S ID]),"")"

Then google Excel remove duplicates of that MATCH row (can't remember what the shortcut key for it is...)

 

[pizzaboy]

Actually... add another column. "=IF([PERSON ID]=[MATCH COLUMN],"DELETE","")

You need to delete all rows that has anything in this new column.


No VBA neccessary!

Of course you could use it to automate the actual deletion of rows if you want...

 

[pizzaboy]

The code to remove duplicates if you're interested...

        Public Sub DeleteBlankRows(col As String)
        Dim rng As Range
        
        On Error Resume Next
        Set rng = Range(col).SpecialCells(xlCellTypeBlanks)
        
        If Not (rng Is Nothing) Then
            rng.Delete shift:=xlUp
        End If
    End Sub

That will remove anything from that MATCH/MAX column that is empty (pass in the column name as a range MyTableName[MATCH COLUMN])...

 

[syedbokhari]

Actually... add another column. "=IF([PERSON ID]=[MATCH COLUMN],"DELETE","")

You need to delete all rows that has anything in this new column.


No VBA neccessary!

Of course you could use it to automate the actual deletion of rows if you want...

I'm currently getting 6000 in those cells ... this is really hard problem because the column A has values which are in column D.

This is just a downsized version of the data the much ... the actual data has like 5000+ rows.

I wrote my own formulas like yours when I started this ... the issue I believe is that values are present in both lists ... so essentially you need a VBA to locate the 'RelativeID' in the cells below column A .... then remove those rows.

So if person ID is 1 (column A) and the relative id is 4 (column B) ... it needs to into the column A find the 4's and delete them of the planet.

I'm crap as hell at explaining things ... hence why I like pictures sometimes :D .

Nice try comrade ! You help is appreciated