delete rows when certain value is met

[zibanitum]

Lets say I have data in in Column D. I have huge range of data in column D. I only want Column D to have "Bad phone #" and "No contact" in it. If the cell doesn't have either of these two in it, I would like to delete that entire row.

I have tried the search function but couldn't find anything that was spefic for my needs.

Does anyone have a formula or macro for this? I appreciate any help.

 

[ajetrumpet]

Loop through it like this (code may not be exact):

dim r as range

  for each r in range("d1", range("d1").end(xldown))
    if r = "bad phone #" OR r = "no contact" then
      GOTO NEXTROW
    ELSE
      r.entirerow.delete
    end if

NEXTROW:
  next r

 

[zibanitum]

I can't get that to work. I thank you for the help.

 

[ajetrumpet]

IF YOU CAN BE MORE SPECIFIC, I CAN HELP YOU, BUT "IT WILL NOT WORK" WON'T GET YOU ANYWHERE. I'M SORRY. PUSH "F8" AND GO THROUGH THE CODE LINE BY LINE AND WATCH THE CURSOR OVER THE CELLS MOVE AS THE CODE RUNS. IF IT ERRORS, TELL ME WHAT LINE, IF IT DOESN'T ERROR, TELL ME WHERE IT DOESN'T DO WHAT IT'S SUPPOSED TO DO. i CAN HELP IF YOU CAN DO THAT FOR ME.

 

[zibanitum]

I apologize for that. When I enter the code into the module and press Alt F8 for the code to run, it doesn't even recognize a code in there. The box that appears when I select Alt F8 is empty.

 

[ajetrumpet]

Alt F8 is the quick jump to the macro list in the excel interface. when you get the popup with an empty window, there is a reason. did you save the file first? if you didn't, that's probably why. i dont know if Alt F8 does anything in the VB session, but as stated above, it has a purpose in the interface spreadsheet window. put the code in, save the project, then save the actual file, and then see if it appears in the macro list for you to run. and put the code behind the actual SHEET that you are working with, not the WORKBOOK module. that will not work.

 

[zibanitum]

I did everything you said and I am still getting a blank window. I appreciate your time on this.

 

[zibanitum]

Sub DeleteRowsWithSpecifiedData()

'Looks in Column D and requires Column IV to be clean

Columns(4).EntireColumn.Insert



With Range("D1:D" & ActiveSheet.UsedRange.Rows.Count)

.FormulaR1C1 = "=IF(RC[1]="""","""",IF(RC[1]=""Not Needed"",NA()))"

.Value = .Value

On Error Resume Next

.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete

End With

On Error GoTo 0

Columns(4).EntireColumn.Delete

End Sub



This code works well for just one value I want deleted, but how do I add more than one value to delete? I just exchange the Not Needed for the value I want deleted. How can I get this to do several values?