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

cainey1991

New Member
Joined
Feb 20, 2014
Messages
31
Hi all,

IDname
a-1red
a-2blue

<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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

cainey1991

New Member
Joined
Feb 20, 2014
Messages
31
I just figured out a solution....
Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Or better

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

cainey1991

New Member
Joined
Feb 20, 2014
Messages
31

ADVERTISEMENT

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

Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
You are missing an IF

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

Aswanth

New Member
Joined
Sep 10, 2014
Messages
3

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
The code I posted has to go on a separate line like this

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top