?
Solved

How to create a Vlookup to Delete Rows in Excel

Posted on 2010-01-11
8
Medium Priority
?
1,203 Views
Last Modified: 2012-05-08
Question. Can I have a Vlookup table in Excel that would automatically delete rows in a specified sheet based on a variable in a specific column?
See attached Excel File. In Column1 on Sheet one I need to delete the entire row where Column1 has the number "9". Is there a way to do this automatically?
Thank you in advance for any help.
Rick
EliminateRows.xls
0
Comment
Question by:ES-Components
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Expert Comment

by:DJR2006
ID: 26283237
If you want to do this as a one off, the quickest way would be to highlight the table. "Filter" it using Data>>Filter and then only show Branch 9.

You can then highlight the rows and delete them and then remove the filter.

Thanks,
David
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 26283494
A function on the spreadsheet can not delete a row, but you can setup a VBA routine that checks a particular range with a code driven Vlookup and deletes rows based on the result.

Leon
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 26283531
Hi,

To answer your question - Can I have a Vlookup table in Excel that would automatically delete rows in a specified sheet based on a variable in a specific column?

The answer is NO. You cannot use the Vlookup function to delete rows automatically. Deleting a row has to be done manually or can be automated by using macros.

You can use a macro which will apply the Vlookup to your columns and then take all the cells with a value of "9" and delete them off.

If you have to do this multiple times, then I would recommend using a macro.
Let me know if you need a macro to do this.

Thanks,
Ardhendu
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Expert Comment

by:patrickab
ID: 26283571
ES-Components,

Step by step for what DJR2006 is suggesting:

1. Place the cursor on cell A1
2. Goto Data/Filter/and select Autofilter
3. Click on the filter down arrow in cell A1, select 'Custom'
4. In the box beside Branch 'equals', select 9 from the dropdown
5. Highlight all the rows shown and press CTRL+- (that's hold down CTRL and press the minus sign) to delete all those rows.

It can all be done with VBA but it's quick to do it this way.

Patrick
0
 

Author Comment

by:ES-Components
ID: 26283794
Okay...
pari123
Can you give me a vlookup formula only that will lookup the Value in Column1 sheet1
compare it to Column1 Sheet2 and return the value in Column2 Sheet2?
Thanks..
Rick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26283969
ES-Components,

As Ardhendu has already said you cannot delete a row by using a native Excel formula.
There is no Sheet2 in your file so there's nothing to compare column1 in Sheet1 with column in Sheet2. No amount of VLOOKUP() formulae will delete a row - it just ain't possible.
If you want a macro to do the job of deleting '9' rows then let us know.
Patrick
0
 
LVL 45

Accepted Solution

by:
patrickab earned 2000 total points
ID: 26284033
ES-Components,

The code below is in the attached file. Press ALT+F8 and run the 'deleter' macro to delete all rows with a 9 in column A.

Hope that helps

Patrick
Sub deleter()
Dim lastrow As Long
Dim i As Long

With Sheets("Sheet1")
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    For i = lastrow To 1 Step -1
        If Cells(i, "A") = 9 Then
            .Rows(i).EntireRow.Delete
        End If
    Next i
End With

End Sub

Open in new window

EliminateRows-01.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26285394
ES-Components - Thanks for the grade - Patrick
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question