[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1601
  • Last Modified:

How to create a Vlookup to Delete Rows in Excel

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
ES-Components
Asked:
ES-Components
1 Solution
 
DJR2006Commented:
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
 
leonstrykerCommented:
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
 
Ardhendu SarangiSr. Project ManagerCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
patrickabCommented:
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
 
ES-ComponentsAuthor Commented:
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
 
patrickabCommented:
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
 
patrickabCommented:
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
 
patrickabCommented:
ES-Components - Thanks for the grade - Patrick
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now