Finding a cell value and deleting the row with that value in it

  • Thread starter Thread starter aintlifegrand79
  • Start date Start date
A

aintlifegrand79

I have a userform that uses a textbox (tbExistingProjectNumber) to find an
existing project number and replace it with a new project number from another
textbox on the same userform (tbChangeProjectNumberTo). My problem is that
if the value in tbExistingProjectNumber starts with a "B" I need it to find
the cell with that value on Sheet9 and delete the entire row that had that
cell in it. Here is my code, thank you in advance for any help you can give.

Private Sub cbChangeButton_Click()
' Activate Sheet9
Sheet9.Activate
' Delete from Sheet9(Budget Proposals)
If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") Then
Sheet9.Columns(1).Find(tbExistingProjectNumber.Value).SelectRow.Delete
Selection.Delete Shift:=x1Up
End If
 
I have a userform that uses a textbox (tbExistingProjectNumber) to find an
existing project number and replace it with a new project number from another
textbox on the same userform (tbChangeProjectNumberTo).  My problem is that
if the value in tbExistingProjectNumber starts with a "B" I need it to find
the cell with that value on Sheet9 and delete the entire row that had that
cell in it.  Here is my code, thank you in advance for any help you can give.

Private Sub cbChangeButton_Click()
'   Activate Sheet9
    Sheet9.Activate
'   Delete from Sheet9(Budget Proposals)
    If CStr(Left(tbExistingProjectNumber.Value, 1) = "B") Then
    Sheet9.Columns(1).Find(tbExistingProjectNumber.Value).SelectRow.Delete
    Selection.Delete Shift:=x1Up
    End If
Click to expand...

Private Sub cbChangeButton_Click()

dim strDelete as String
Sheet9.Activate

' Delete from Sheet9(Budget Proposals)
strDelete = CStr(Left(tbExistingProjectNumber.Value, 1))

If strdelete = "B" Then
Sheet9.Columns(1).Find(strDelete).SelectRow
Selection.Delete Shift:=xlShiftUp
End If
End Sub


Does that work better?

Chris
 
Thanks for the help, but no that didn't work I got the debugger on the line:

Sheet9.Columns(1).Find(strDelete).SelectRow

Any other suggestions?
 
Untested, uncompiled.

Option explicit
Private Sub cbChangeButton_Click()
dim FoundCell as range
If ucase(Left(tbExistingProjectNumber.Value, 1)) = "B" Then
with Sheet9.Columns(1)
'I'd include all the parms to the .find statement!
set foundcell = .cells.Find(tbExistingProjectNumber.Value)
end with
if foundcell is nothing then
'not found
else
foundcell.entirerow.delete
end if
End If
End sub
 
Back
Top