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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aintlifegrand79 - 14 Apr 2008 16:41 GMT

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

Reply to this Message

cht13er - 14 Apr 2008 17:09 GMT

On Apr 14, 11:41 am, aintlifegrand79
<aintlifegran...@discussions.microsoft.com> wrote:

> 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
[quoted text clipped - 11 lines]
> Selection.Delete Shift:=x1Up
> End If

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

Reply to this Message

aintlifegrand79 - 14 Apr 2008 17:38 GMT

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?

> On Apr 14, 11:41 am, aintlifegrand79
> <aintlifegran...@discussions.microsoft.com> wrote:
[quoted text clipped - 31 lines]
>
> Chris

Reply to this Message

Dave Peterson - 14 Apr 2008 18:13 GMT

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

Signature

Dave Peterson

Reply to this Message

aintlifegrand79 - 14 Apr 2008 22:34 GMT

Dave seems to have worked, thank you very much for your help.

> Untested, uncompiled.
>
[quoted text clipped - 29 lines]
> > Selection.Delete Shift:=x1Up
> > End If

Reply to this Message