NEED HELP!! Looking to delete a Row Range instead of entire row!!PLEASE HELP!!

[danemorel]

Hi there,

Question! I have used VBA Userform to create a delete entry button with data entry from Column (B:F). It is currently set to delete the entire row but i only want the Row from Range (columns B to F), that the active cell is in to be deleted.

what is the code needed?

i.e. instead of : ActiveCell.EntireRow.Delete" , what do i need to use if i want a row ranged from columns B to F deleted from the active cell being in anyone of the cells from B to F ?

your response will be highly apprecaited

[BadlySpelledBuoy]

You could use:

Please Login or Register  to view this content.

BSB

[danemorel]

Thanks BSB.. but it doesnt seem to do anything. my current command button is coded as follows:

Private Sub CommandButtonDelete_Click()

Dim sh As Worksheet
set sh = Msgbox (:do you want to delete this record?", vbquestion + vbYesNo)
If pk = 7 then exit sub
sh.Unprotect " 0000"

Range("B" & ActiveCell.Row & ":F" & ActiveCell.Row).Delete Shift:=x1Up
sh.protect"0000"

End If
End Sub

[danemorel]

Thanks BSB.. but it doesnt seem to do anything. my current command button is coded as follows:

Private Sub CommandButtonDelete_Click()

Dim sh As Worksheet
set sh = ThisWorkBook.sheets(:Data")
dim pk as integer
If ActiveCell.Row >2 then
pk = Msgbox (:do you want to delete this record?", vbquestion + vbYesNo)
If pk = 7 then exit sub
sh.Unprotect " 0000"

Range("B" & ActiveCell.Row & ":F" & ActiveCell.Row).Delete Shift:=x1Up
sh.protect"0000"

End If
End Sub

[BadlySpelledBuoy]

Could you attach a desensitized version of your workbook?

BSB

[danemorel]

to give you more details. I know my data will populate each row from B to F with Data of one entry. but i want the Row (ranged from B to F) that my activeCell is in to be deleted. i.e. if i want my row 18 to be delete, i should be able to select any column in row 18 Between B and F and it should delete the entire row.

[BadlySpelledBuoy]

I understand that, and the line of code I provided will do exactly that.

Without a sample workbook it difficult to diagnose why it's not working for you.

One thing I do see from the code you posted is a typo. You have "x1Up" rather than "xlUp".
Change that and try it. If that still doesn't work then post the sample workbook.

BSB

[danemorel]

Thank you!!! it worked! thanks so much

[BadlySpelledBuoy]

Happy to help

Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

BSB

[danemorel]

so that worked, but now i want to be able to do the same but with columns G to L. I copy pasted the code, but if there are entries for "concrete" in col B to F, row 2 and entries for "steel", in col G to L, Row 2, and i just want to delete the entry foir steel in row 2 (col G to L), what would be the coding then?

[BadlySpelledBuoy]

Do you mean using the same button?

BSB

[danemorel]

yes using the same button

[BadlySpelledBuoy]

Replace that line of code with the below:

Please Login or Register  to view this content.

BSB

[danemorel]

yes, and so if im in the range of b to f, it will delete only the row i.e. 18 (g to f), but if i then go to row 16, in between G and L, then it will only delete 16 (G to L)

[danemorel]

Thank you!! this solved it! how do i mark as solved?

[BadlySpelledBuoy]

Excellent news

Go back to your first post in the thread and just above that it says "Thread Tools". Click on that and select SOLVED.

Have a good one.

BSB