Excel Formula Help - How to Delete a Row

Microsoft Excel 2002 (full product)
August 31, 2009 at 12:33:03
Specs: Windows XP, 512MB
I have an Excel workbook in which there are 2 spreadsheets; sheet1 contains a vendor list – Column A – G | #, Printer Name, Address 1, Address 2, City/State/Zip, Attn, Email and sheet2 is where you type in a vendor number and the address appears
I currently use formula “=VLOOKUP($D$1,'Vendor List'!$A$3:$G$26,2,FALSE)”on Sheet2(columnG row 4) and copied in each cell below till column G row 9. I’ve changed the Col_Index number in the formula to identify the appropriate column in sheet 1. So, for example, if you type “1” in D1 on sheet 2 : Cells G4 – G9 will place:
XYZ Company
453 Smith Street
New York, NY 11220
Attn: John

My issue is if I don’t have an address for Address 2, is gives me a “0”

I would like to have Row 6 deleted if these is no address 2.
Does anyone know of a better function to use or how to go about removing that row?
Note: I have not used VBA before

See More: Excel Formula Help - How to Delete a Row

Report •

August 31, 2009 at 13:28:19
There is no function to delete a row. You would need to use VBA.

Do you really want to delete the row or would you be satisfied with an empty cell if there is no address 2? If so, use an IF statement to check and see if the VLOOKUP returns 0. If it does, put "" (nothing) in the cell. If it doesn't return 0, then put the results of the VLOOKUP in the cell.

=IF(VLOOKUP($D$1,'Vendor List'!$A$3:$G$26,2,0)=0,"",VLOOKUP($D$1,'Vendor List'!$A$3:$G$26,2,0))

If you really want to delete the row, you could use a Worksheet_Change macro like this one:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Range("G6") = 0 Then
  Application.EnableEvents = False
  Application.EnableEvents = True
 End If
End Sub

Every time a change is made to the sheet, the code will check G6 and if it's 0, it will delete the entire row.

Report •

August 31, 2009 at 15:13:31

Thank you for your response. I will give this a try and see if it works. I will try the macro, because I would like the cell to be removed if no address is in "address 2".

Thanks Again

Report •

September 1, 2009 at 06:35:43

The macro works. But here is the tricky part - If there is an address in the address 2 line and on worksheet 2 it does not put a "0" but the address, how can that same macro place that line. What is does now is it that cell is "0" it removes it and since it is removed, if there is an address it does not appear.

Would you use an "If,else" script?

Also, how would you write this?

Greatly Appreciate the help.

Report •

Related Solutions

September 1, 2009 at 07:55:12
I guess I should have asked, but when you said you wanted to delete the Address2 line, I had assumed that once you brought the data in, you were done that spreadsheet and wouldn't be loading another address into it. Sort of like opening a invoice template that had all of the formulae, loading in the address and then saving it as a new file.

The obvious problem is that the code deletes the VLOOKUP function in G6 so it can't pull that data next time.

Of course, you can't have it both ways. You can't have VLOOKUP pull data from a lookup_array, delete the row with the formula and then have it available again next time.

However, I've come up with a formula based workaround that gets the job done. If you don't like this solution, the only other option I can suggest is to do it all with VBA, but then you get into code maintenance issues and all that.

Here's what worked for me:

Take the VLOOKUP formulae from G4:G9 and put them someplace else. You can put them anywhere you want, even on another sheet, in hidden columns, wherever.

I'll use A4:A9 just as an example.

Now, using the example data from your OP, A4:A9 will display:

4        XYZ Company
5        453 Smith Street 
6        0
7        New York, NY 11220
8        Attn: John
9        john@yahoo.com

In G4:G9, put these formulae:

4	=A4
5	=A5
6	=IF($A$6=0,A7,A6)
7	=IF($A$6=0,A8,A7)
8	=IF($A$6=0,A9,A8)
9	=IF($A$6=0,"",A9)

G4 and G5 will always display what is in A4 and A5. G6:G9 will display what is in A6:A9 unless there is a 0 in A6. In that case the data from A7:A9 will be shifted up 1 row and G9 will be empty.

Report •

September 1, 2009 at 08:17:26
P.S. You could still use the VLOOKUP in G4 and G5, but I moved them all just for consistancy sake.

This method keeps your actual VLOOKUP data in one place in case you ever want to make changes to how the data is pulled. It just keeps the spreadsheet "neater".

Report •

September 1, 2009 at 11:13:00
I have to say this was the first I've used a forum and you are a genius.

The solution provided worked perfectly.

I'm really interested in learning more about using Macros in Excel. Do you recommend a site or book that has helped you in the past?

Thank You Once Again!

Report •

September 1, 2009 at 11:57:35
I've actually never read a book on VBA or Excel, although I hear that there are a lot of good ones out there.

Just about everything I have learned has been through trial and error while using Excel and by answering questions in forums like this one.

If I see something I don't know how to do, I try some stuff, combine things I've done before and take advantage of Google which points me towards other forums and sites that may not give the direct answer, but provide hints at similar issues which I then mold to fit the specific question being asked.

Report •

Ask Question