Delete Depending In Inputbox Value

Question

Hi All

I am having a problem with the following Macro below. I am trying to delete specific rows according to the value or text in the inputbox function. 

I have a column on which I insert the document reference number the column is "B". So when running the Macro the first step is that I enter the document reference number this could be variable that includes text and numbers. Than the response exams the data and were it matches it deletes data. This thing is that it does not work on demand sometimes it does sometimes it doesn't.

Please find below the Macro;

Sub deletion()

Dim lastrow As Long, X As Long

Dim respone As Long

lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

Response = Application.InputBox("Enter Document Reference Number", "Document Reference", Type:=1)

If Response = 0 Then

MsgBox prompt:="You Pressed Cancel", Buttons:=vbInformation + vbOKOnly, Title:="Cancel"

Exit Sub

End If

 For X = lastrow To 6 Step -1

     If (Cells(X, 2)) <> Response Then

    Exit Sub

     End If

     If (Cells(X, 2)) = Response Then

         Cells(X, 11).Offset(, -10).Resize(, 11).Delete Shift:=xlUp

     End If

 Next

End Sub

Thanks 

Owen

Answer 1

Hi,

try this..

(make a copy before...)

Sub deletion2()

Dim Lastrow As Long, X As Long

Dim Response As Long

Lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row

Response = Application.InputBox("Enter Document Reference Number", "Document Reference", Type:=1)

If Response = 0 Then MsgBox prompt:="You Pressed Cancel", Buttons:=vbInformation + vbOKOnly, Title:="Cancel": Exit Sub

For X = Lastrow To 6 Step -1

If (Cells(X, 2)) = Response Then

Cells(X, 1).Resize(, 11).Delete Shift:=xlUp

End If

Next

End Sub

Answer 2

Hi Owen,

In your For loop, your first condition    If (Cells(X, 2)) <> Response Then  

why are you exiting the Sub ?

Are you not interested to check for the next X value ?

 For X = lastrow To 6 Step -1

     If (Cells(X, 2)) <> Response Then

Exit Sub

     End If

    .....