View Full Version : Solved: Help with Find/Delete Entire Row

02-22-2013, 09:51 AM

I have a VBA code that brings up an inputbox, searches for this RP# (which has to be in text form) and deletes the entire row that has the RP# in it

Sub Override()
Dim RPtoDel As String
Dim delrows As Long
RPtoDel = InputBox("Enter RP#")
Do While True
On Error GoTo Err_Handle
Worksheets("PY Query").Select
Range("P:P").Find(What:=RPtoDel, LookIn:=xlValues, LookAt:=xlWhole, After:=ActiveCell).EntireRow.Delete Shift:=xlUp
delrows = delrows + 1
MsgBox "Number of Deleted Rows - " & delrows
Worksheets("Ex Resident").Select
End Sub

Unfortunately it is not finding the RP# in the list. I have double checked to make sure the RP#'s are in the list and they are. I am wondering if it has to do with the number having to be in text form (which it has to be since the query upload to the excel sheet has the RP# in text). Thanks for your help

02-22-2013, 12:54 PM
Would this do it? (On a copy of your workbook)

Sub Try_This()
Dim RPtoDel As String
Dim delrows As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
RPtoDel = InputBox("Enter RP#")
With Worksheets("PY Query")
.Columns(16).Replace (RPtoDel), "", xlPart
End With
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

02-22-2013, 01:18 PM
Unfortunately it did not work. It showed a Run-Time error '1004': No cells were found.

02-22-2013, 01:21 PM
Please disregard the last reply, it actually did work!! Thanks so much for your help jolivanes :clap: