Delete Row Based on Row Position

syedbokhari

Board Regular
Joined
Aug 19, 2013
Messages
94
Hi Guys,

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

HH14X9C.png


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.

HH18Kte.png


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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
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.

HH1DGoLh.png


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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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...)
 
Upvote 0
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...
 
Last edited:
Upvote 0
The code to remove duplicates if you're interested...

Code:
        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])...
 
Last edited:
Upvote 0
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...

HH2ODpT.png


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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top