Deleting cells on a certain condition

[marino3d]

Hi,

I'm looking to create a macro where someone could enter a column and a value and all rows that contain that value in that column will be erased. I was browsing old posts on this board and I found something that looks good but has one fatal error. Here's the code:

Sub myDeleteRows()

Dim MyCol As String
Dim MyVal As Variant
Dim i As Integer

MyCol = InputBox("column to look through", "Column Search", "A")
MyVal = InputBox("Value to look for", "search value", 0)
  For i = 1 To Range(MyCol & "65536").End(xlUp).Row Step 1
    If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), MyVal) > 0 Then
    Range("A" & i).EntireRow.Delete
    End If
  Next i
  
End Sub

This works well, unless my value is 0 which comes up a lot. If I enter say column V and value 0 It deletes rows at random instead of just the rows that contain 0. That is not good. Any ideas how to fix this?

I am running Windows 7 and Office 2010.

 

[JackDanIce]

Then change mine to:

Sub DeleteVisibleRows()
Dim Msg as string
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Msg = "Please confirm you have applied a filter and are ready to delete"
If MsgBox(msg, vbYesNo) <> vbYes Then
  Msg = "Please filter your data and then re-run this macro") & vbcrlf & vbcrlf
  Msg = Msg & "Macro now stopping"
  Msgbox Msg
  Exit Sub
End If
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
ActiveSheet.AutoFilterMode = False
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

And yes, works for any filtered data i.e. deletes whatever is visible after it's been filtered