VBA: Search for a user input string in table & delete the row containing that value

[cainey1991]

Hi all,

ID	name
a-1	red
a-2	blue

<tbody>
</tbody>

I am wanting a input box to appear and the user types in a string "a-2" and it will then search the table "table1" and delete the row containing that string.

Any help would be appreciated.

D

 

[cainey1991]

I just figured out a solution....

Sub Button7_Click()

Dim MyCol As String
Dim i As Integer


Dim riskID As String
riskID = InputBox( _
prompt:="Type the ID you wish to delete", _
title:="Delete ID", _
Default:="Type ID")
If Len(riskID) = 0 Then
MsgBox "No file name chosen"
End If


For i = 1 To Range("B" & "65536").End(xlUp).Row Step 1
If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), riskID) > 0 Then
Range("B" & i).EntireRow.Delete
End If
Next i


End Sub

My next question is how can I add a confirmation to the delete function? As in, asking the user if they really want to do this?

Thanks,
D

 

[VoG]

Try

If MsgBox("Are you sure", vbYesNo + vbQuestion) = vbYes Then Range("B" & i).EntireRow.Delete

 

[VoG]

Or better

If MsgBox("Are you sure you want to delete row " & i, vbYesNo + vbQuestion) = vbYes Then Range("B" & i).EntireRow.Delete

 

[cainey1991]

Where would I place this in the code?
I am trying to put it into :

Sub Button8_Click()


Dim MyCol As String
Dim i As Integer




Dim riskID As String
riskID = InputBox( _
prompt:="Type the ID you wish to delete", _
title:="Delete ID", _
Default:="Type ID")
If Len(riskID) = 0 Then
MsgBox "No file name chosen"
End If




For i = 1 To Range("B" & "65536").End(xlUp).Row Step 1
If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), riskID) > 0 Then
MsgBox("Are you sure you want to delete row " & i, vbYesNo + vbQuestion) = vbYes Then Range("B" & i).EntireRow.Delete
End If
Next i


End Sub

I am getting syntax errors with this though....

 

[VoG]

You are missing an IF

If MsgBox("Are you sure you want to delete row " & i, vbYesNo + vbQuestion) = vbYes Then Range("B" & i).EntireRow.Delete

 

[Aswanth]

Hi I am here for one solution.
I am having different .xls files and i want VBA coding to merge all data into one excel sheet and have to delete last three rows in every sheet.

Can any one help me please.

 

[VoG]

Hi I am here for one solution.
I am having different .xls files and i want VBA coding to merge all data into one excel sheet and have to delete last three rows in every sheet.

Can any one help me please.

Please start your own thread for this unrelated question.

 

[cainey1991]

Just for my own sanity...

I already have an if, can I have two ifs?

 

[VoG]

The code I posted has to go on a separate line like this

For i = 1 To Range("B" & "65536").End(xlUp).Row Step 1
    If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), riskID) > 0 Then
        If MsgBox("Are you sure you want to delete row " & i, vbYesNo + vbQuestion) = vbYes Then Range("B" & i).EntireRow.Delete
    End If
Next i

In general you'll find it easier to follow the logic if you indent the code, as above.