VBA If a Value is Present, Then Delete Row

[Littlemalky]

I have data in columns "A:H". I have part numbers in column A. There is a header in the first row. I'm running a vLOOKUP, in columns "D:H", against the part numbers in column A, and part numbers in 5 different files. If those part numbers exist on the other workbooks, a value will be present. All the other cells will have the typical "#N/A".

I need to exclude all the part numbers that have a value in their row between columns "D:H". Is there a way to write code to delete the row completely if a value comes up between D:H? I won't need that part number if it's already being used in one of these 5 workbooks.

P.S. There are quantities in column C all the way down, so it's important to note the D:H part. The amount of rows is always going to be different everyweek so I need it to search to the last row.

P.S.S. I'm using Excel 2007.

 

[Michael M]

One way
Change your VLOOKUPS to eliminate the error and put a value in instead
eg, change to suit and copy down

=IF(ISNA(VLOOKUP(C1,A2:A15,1,FALSE)),"Part Required",VLOOKUP(C1,A2:A15,1,FALSE))

then use this code

Sub REMOVE()
Dim r As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = lr To 2 Step -1
        If Range("D" & r).Value > 0 And Range("E" & r).Value > 0 _
        And Range("F" & r).Value > 0 And Range("G" & r).Value > 0 _
        And Range("H" & r).Value > 0 Then
            Rows(r).Delete
        End If
    Next r
End Sub

 

[Littlemalky]

Thank you for your help; however, it seems to be deleting all the data instead of just the rows with numbers > 0. There should be over 100 rows left that don't have values. I changed your verbage in the vlookup to say "Here" instead. Then I copy/pasted values so that the calculations would run faster. Here is my code:

Range("D1").Select
    Selection.FormulaR1C1 = "NR"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],qryNewReleasesDomestic.xlsx!C1:C3,3,0),""Here"")"
    Range("E1").Select
    Selection.FormulaR1C1 = "Allo"
    Range("E2").Select
    Selection.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-4],QryAlloDomestic.xlsx!C1:C3,3,0),""Here"")"
    Range("F1").Select
    Selection.FormulaR1C1 = "Disco"
    Range("F2").Select
    Selection.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-5],QryDiscoDomestic.xlsx!C1:C3,3,0),""Here"")"
    Range("G1").Select
    Selection.FormulaR1C1 = "Polar"
    Range("G2").Select
    Selection.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-6],QryPolarDomestic.xlsx!C1:C3,3,0),""Here"")"
    Range("H1").Select
    Selection.FormulaR1C1 = "Photo"
    Range("H2").Select
    Selection.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-7],QryPhotoDomestic.xlsx!C1:C3,3,0),""Here"")"
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
        Range("D2:H2").AutoFill Destination:=Range("D2:H" & LastRow)
        Columns("D:H").EntireColumn.AutoFit
    Columns("D:H").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[sparky101747]

Check this page out to see if it helps. I use this to delete rows that contain certain values.

http://www.xtremevbtalk.com/showthread.php?t=300757

 

[Littlemalky]

Thank you, I'll look into that.

 

[Littlemalky]

Strange, none of those seems to be working for me.

 

[sparky101747]

Do you mean they don't do what you want or they do nothing at all.

Note: before using thos macros you need to insert the public function on the first page that comes up when going to link

 

[Littlemalky]

Yeah, I tried using a couple of the different suggestions and they don't seem to be working for me. I've attached the function on the end too.

 

[sparky101747]

I'm not an expert by any means I just pass along info that was useful to me, but I put the function first or by itself and it works for me. So thats about all I know, sorry.