deleting macro issue

[kelly mort]

I have this macro here that I use to delete from my worksheet. I just found out that when I delete, the numbering I used is not updated. So I have commented that out in the code below:

 findvalue.Value = ""

now what I wanna do is after deleting and sorting the data excluding the column B, I want to go down column B to the last cell with data and delete that number. I need your help with this one. I see it as a challenge but I know you see it as a hobby. Thanks
Kelly

Private Sub CmdDelete_Click()
    Dim sht As String, findvalue, answer
    sht = ComboBox1.Value
    On Error GoTo errHandler
    If Rw2.Value = "" Or Rw3.Value = "" Then
        MsgBox "There is no data to delete. Select data first"
        Exit Sub
    End If
    answer = MsgBox("Are you sure you want to delete the name: " & Rw2.Value & "?", vbYesNo + vbQuestion, _
    "Delete this name")
    If answer = vbYes Then
    Set findvalue = Sheets(sht).Range("B7:B10000").Find(What:=Me.Rw1, LookIn:=xlValues)
        ‘findvalue.Value = ""
            findvalue.Offset(0, 1).Value = ""
                findvalue.Offset(0, 2).Value = ""
                    findvalue.Offset(0, 3).Value = ""
                        findvalue.Offset(0, 4).Value = ""
                            findvalue.Offset(0, 5).Value = ""
                                findvalue.Offset(0, 6).Value = ""
                            findvalue.Offset(0, 7).Value = ""
                        findvalue.Offset(0, 8).Value = ""
                    findvalue.Offset(0, 9).Value = ""
                findvalue.Offset(0, 10).Value = ""
            findvalue.Offset(0, 11).Value = ""
                Else
            MsgBox "Deletion cancelled", vbInformation, "Deletion Alert"
            Exit Sub
        End If
        
        SortIt

            MsgBox "Deletion successful", vbInformation, "Deletion Alert"
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An error has occured"
End Sub

 

[kelly mort]

Actually, this is the code that worked, when you gave the first code I only saw the red part so changed only that part and it worked. So when the second came, I looked carefully and saw that I did not enter the exact one you gave. #some_mistakes_are_cool
Kelly

Private Sub CmdDelete_Click()
    Dim sht As String, findvalue, answer, i As Integer
    sht = ComboBox1.Value
    On Error GoTo errHandler
    If Rw2.Value = "" Or Rw3.Value = "" Then
        MsgBox "There is no data to delete. Select data first"
        Exit Sub
    End If
    If ComboBox1.Value = "" Then
        MsgBox "There is no Sheet to delete from. Select datasheet first"
        Exit Sub
    End If
    answer = MsgBox("Are you sure you want to delete the name: " & Rw2.Value & "?", vbYesNo + vbQuestion, _
    "Delete this name")
    If answer = vbYes Then
    Set findvalue = Sheets(sht).Range("B7:B10000").Find(What:=Me.Rw1.Value + 1, LookIn:=xlValues)
     
        For i = 1 To 20
            findvalue.Offset(0, i).Value = ""
        Next i
       
        Else
            MsgBox "Deletion cancelled", vbInformation, "Deletion Alert"
            Exit Sub
        End If
        SortIt
        Cells(Rows.Count, "B").End(xlUp).ClearContents
            MsgBox "Deletion successful", vbInformation, "Deletion Alert"
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An error has occured"
End Sub

 

[footoo]

Did you try the macros I posted?

 

[kelly mort]

Did you try the macros I posted?

Yes but they were not getting the workdone. The first one will delete only the content in B and leave the others

 

[footoo]

Yes but they were not getting the workdone. The first one will delete only the content in B and leave the others

Did you copy the code correctly?

In the first macro this clears the contents in the findValue row from column C to column M :
findvalue.Offset(0, 1).Resize(, 11).ClearContents

And in the second macro this clears the contents in the findValue row from column B to column M :
findvalue.Resize(, 12).ClearContents

 

[kelly mort]

Oh okay the first macro is doing that but the second clears in column B first which stops the sequential numbering. I did not noticed u modified the msgbox prompt in the code in the first place.
Thank you very much
Kelly