Deleting entire row.... final question

[Pam Field]

Thanks again to anyone who has helped me with this spreadsheet so far. I'm
sure this will be my last question.

I have created a spreadsheet with a command button that leads to a form to
remove a child from the spreadsheet. I have most of it working but cannot
work out how to delete the whole row of info re the child - my macro only
deletes the cell with the child's name in it and moves everything in that
column up.

Excuse the clumsiness of the code as I am a learner:

Private Sub cmdRemChild_Click()
Dim myCell As Range
Dim ChosenName As String
Dim NameFound As Boolean
Dim YesNo As Integer

ChosenName = cboChildName.Text

If Len(ChosenName) = 0 Then
MsgBox ("Please Select or Enter A Name")
cboChildName.SetFocus
Exit Sub
End If

Sheets("Child Records").Select

NameFound = False
For Each myCell In Range("Name_of_Child")
If myCell.Value = ChosenName Then
myCell.Select
NameFound = True
YesNo = MsgBox("Are you sure you want to remove this child?",
vbYesNo + vbExclamation, "Caution")

Select Case YesNo
Case vbYes
Selection.Delete Shift:=xlUp
Range("A6").Select

Case vbNo
Range("A6").Select
End Select
Unload Me
Exit For
End If
Next myCell

If NameFound = False Then
MsgBox "Name not Found!"
cboChildName.SetFocus
Exit Sub
End If

End Sub

Your help would be greatly appreciated.
Kind Regards
Pam

[Chris Lavender]

Hi Pam

You need to replace

Selection.Delete Shift:=xlUp

with

Selection.EntireRow.Delete

Best rgds
Chris Lav

"Pam Field" <pam@pamfield.com> wrote in message
news:44c1f4e0$1@quokka.wn.com.au...
> Thanks again to anyone who has helped me with this spreadsheet so far.
> I'm sure this will be my last question.
>
> I have created a spreadsheet with a command button that leads to a form to
> remove a child from the spreadsheet. I have most of it working but cannot
> work out how to delete the whole row of info re the child - my macro only
> deletes the cell with the child's name in it and moves everything in that
> column up.
>
> Excuse the clumsiness of the code as I am a learner:
>
> Private Sub cmdRemChild_Click()
> Dim myCell As Range
> Dim ChosenName As String
> Dim NameFound As Boolean
> Dim YesNo As Integer
>
> ChosenName = cboChildName.Text
>
> If Len(ChosenName) = 0 Then
> MsgBox ("Please Select or Enter A Name")
> cboChildName.SetFocus
> Exit Sub
> End If
>
> Sheets("Child Records").Select
>
> NameFound = False
> For Each myCell In Range("Name_of_Child")
> If myCell.Value = ChosenName Then
> myCell.Select
> NameFound = True
> YesNo = MsgBox("Are you sure you want to remove this child?",
> vbYesNo + vbExclamation, "Caution")
>
> Select Case YesNo
> Case vbYes
> Selection.Delete Shift:=xlUp
> Range("A6").Select
>
> Case vbNo
> Range("A6").Select
> End Select
> Unload Me
> Exit For
> End If
> Next myCell
>
> If NameFound = False Then
> MsgBox "Name not Found!"
> cboChildName.SetFocus
> Exit Sub
> End If
>
> End Sub
>
> Your help would be greatly appreciated.
> Kind Regards
> Pam
>
>

[Pam Field]

Thanks so much Chris,

I figured it wouldn't be too difficult but I just couldn't work it out.

Have a lovely weekend
Pam

"Chris Lavender" <chrislavender@talktalk.net> wrote in message
news:eG7ti%23XrGHA.4992@TK2MSFTNGP05.phx.gbl...
> Hi Pam
>
> You need to replace
>
> Selection.Delete Shift:=xlUp
>
> with
>
> Selection.EntireRow.Delete
>
> Best rgds
> Chris Lav
>

[Don Guillett]

a bit simpler

Sub deletechildrow()
Rows(Range("nameofchild").Find("a").Row).Delete
End Sub

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Pam Field" <pam@pamfield.com> wrote in message
news:44c1f4e0$1@quokka.wn.com.au...
> Thanks again to anyone who has helped me with this spreadsheet so far.
> I'm sure this will be my last question.
>
> I have created a spreadsheet with a command button that leads to a form to
> remove a child from the spreadsheet. I have most of it working but cannot
> work out how to delete the whole row of info re the child - my macro only
> deletes the cell with the child's name in it and moves everything in that
> column up.
>
> Excuse the clumsiness of the code as I am a learner:
>
> Private Sub cmdRemChild_Click()
> Dim myCell As Range
> Dim ChosenName As String
> Dim NameFound As Boolean
> Dim YesNo As Integer
>
> ChosenName = cboChildName.Text
>
> If Len(ChosenName) = 0 Then
> MsgBox ("Please Select or Enter A Name")
> cboChildName.SetFocus
> Exit Sub
> End If
>
> Sheets("Child Records").Select
>
> NameFound = False
> For Each myCell In Range("Name_of_Child")
> If myCell.Value = ChosenName Then
> myCell.Select
> NameFound = True
> YesNo = MsgBox("Are you sure you want to remove this child?",
> vbYesNo + vbExclamation, "Caution")
>
> Select Case YesNo
> Case vbYes
> Selection.Delete Shift:=xlUp
> Range("A6").Select
>
> Case vbNo
> Range("A6").Select
> End Select
> Unload Me
> Exit For
> End If
> Next myCell
>
> If NameFound = False Then
> MsgBox "Name not Found!"
> cboChildName.SetFocus
> Exit Sub
> End If
>
> End Sub
>
> Your help would be greatly appreciated.
> Kind Regards
> Pam
>
>