Macro - delete entire row if cell contains specific value

hi folks,

i have a macro which deletes the entire row when in column A is a specific value, in my case 555555.
can anybody please tell me how i can add specific values in the macro.

know the macro is deleting rows when in column A is value 555555, no i want
to delete also rows with 666666, and 888888. is there a possibility to "refresh" the macro
when i have to add new sped. values?

here is the macro:

Sub myDeleteRows()

Dim MyCol As String
Dim i As Integer
For i = 1 To Range("A" & "65536").End(xlUp).Row Step 1
If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), "555555") > 0 Then
Range("A" & i).EntireRow.Delete
End If
Next i

End Sub



thx in advance!!

mandura

you can change the if like so:

If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), "555555") > 0 or Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), "666666") > 0 Or Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), "777777") > 0 then

unfortunately not working?
probably i am pasting your if formala the wrong way?

can you give me the whole macro please?

thank you very much!!

mandura.

Could you post a sample workbook?

Originally Posted by Crispy85

Could you post a sample workbook?

thats not necessary, problem is solved.

thank you very much!!

Maybe:

Sub manduraz()
Range("A1:A" & Range("A" & Rows.Count).End(3).row).AutoFilter 1, 555555, xlOr, 666666
Range("A2:A" & Range("A" & Rows.Count).End(3).row).SpecialCells(12).EntireRow.Delete
Range("A1:A" & Range("A" & Rows.Count).End(3).row).AutoFilter 1, 888888
Range("A2:A" & Range("A" & Rows.Count).End(3).row).SpecialCells(12).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub

Or

Sub manduray()
Dim x As Long
x = InputBox("Please Enter a Number to Delete")
Range("A1:A" & Range("A" & Rows.Count).End(3).row).AutoFilter 1, x
Range("A2:A" & Range("A" & Rows.Count).End(3).row).SpecialCells(12).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub

John H. Davis,

both macros are working perfectly!

i tried in the first macro to add 444444 as spec. value, and it works to.
i just have to copy the row "Range("A1:A" & Range("A" & Rows.Count).End(3).row).AutoFilter 1, 888888"
and paste it under and change 888888 to 444444, so i can refresh the macro and add new values.

thank you very much you helped me a lot!

cheers!

You're welcome. Glad to help out and thanks for the feedback. Please mark this thread as solved if you're satisfied with the solution given.

I am sorry,

obviously i did something wrong.
i wrote down all the numbers when they are in column A i want to delete entire rows,
but it doesnt work.
can you tell me what i did wrong? it works, but only for two values that are on the top??

Sub manduraz()
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 99810, xlOr, 99820
Range("A2:A" & Range("A" & Rows.Count).End(3).Row).SpecialCells(12).EntireRow.Delete
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 99837
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 99838
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 99840
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 99841
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 99842
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 99844
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 99845
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 99846
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 99850
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 99885
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 295038
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 295039
Range("A1:A" & Range("A" & Rows.Count).End(3).Row).AutoFilter 1, 295040
Range("A2:A" & Range("A" & Rows.Count).End(3).Row).SpecialCells(12).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub

John H. Davis,

sorry for breaking the rules! didnt know that. next time i will do it like according to the rules.

the macro you wrote works perfectly!

thank you very, very much for your help and patience!

mandura

You're welcome. Glad to help out and thanks for the feedback.