Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Delete Row Based on Row Position
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular syedbokhari's Avatar
    Join Date
    Aug 2013
    Location
    South West London
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Delete Row Based on Row Position

    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

  2. #2
    Board Regular
    Join Date
    Feb 2006
    Posts
    3,566
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Row Based on Row Position

    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.

  3. #3
    Board Regular syedbokhari's Avatar
    Join Date
    Aug 2013
    Location
    South West London
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Row Based on Row Position

    Quote Originally Posted by ravishankar View Post
    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

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Row Based on Row Position

    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 by pizzaboy; Oct 5th, 2018 at 06:33 AM.

  5. #5
    Board Regular syedbokhari's Avatar
    Join Date
    Aug 2013
    Location
    South West London
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Row Based on Row Position

    Quote Originally Posted by pizzaboy View Post
    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

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Row Based on Row Position

    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...

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Row Based on Row Position

    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...)

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Row Based on Row Position

    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 by pizzaboy; Oct 5th, 2018 at 07:34 AM.

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Row Based on Row Position

    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 by pizzaboy; Oct 5th, 2018 at 07:41 AM.

  10. #10
    Board Regular syedbokhari's Avatar
    Join Date
    Aug 2013
    Location
    South West London
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete Row Based on Row Position

    Quote Originally Posted by pizzaboy View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •