Select a cell to then delete cells to the right

[ipbr21054]

Hi,
I have a list of values where currently the range is N4:R52 but in time it will go futher down the page.
I would like to select a value in column N & then be able to delete that value & also the values to the right of it thus being column O,P,Q & R

So select cell N20
Then delete values in cells N20:R20

Many thanks if you could advise where to start as i will then add some Msgbox etc afterwards.

 

[Joe4]

This will clear the contents from the current cell, and from the 4 columns to the right of that cell:

Sub MyDelete()
    Range(ActiveCell, ActiveCell.Offset(0, 4)).ClearContents
End Sub

 

[Rick Rothstein]

Here is another macro that will also work...

Sub MyDelete2()
  ActiveCell.Resize(1, 5).ClearContents
End Sub

 

[ipbr21054]

Thanks,
Two things ive noticed.


Here is my msgbox code
I select the cell in column N
I then see the msgbox BUT selecting NO & YES both does nothing at all & no error.
I swapped the code around but then vbNo deleted the values ?

Ive noticed that this will also work for anywhere on the sheet,can it be made specific that the selected value MUST be in column N to start with.

Thanks

Private Sub DeleteCustomer_Click()
    MsgBox "ARE YOU SURE YOU WISH TO DELETE THIS CUSTOMER", vbYesNo + vbCritical, "DELETE GRASS CUTTING CUSTOMER"
    If vbNo Then
    Exit Sub
    Else
    Range(ActiveCell, ActiveCell.Offset(0, 4)).ClearContents
    End If
End Sub

 

[kvsrinivasamurthy]

Try this code

Sub DeleteValues()
Dim DelVal As Long, LR As Long
Dim Frng As String
Dim M
LR = Range("N" & Rows.Count).End(xlUp).Row
DelVal = Val(InputBox("Enter the value to find:"))
M = Filter(Evaluate("transpose(IF(N4:N" & LR & "=" & DelVal & ",Row(N4:N" & LR & "),False))"), False, False)
For T = 0 To UBound(M)
Range("N" & M(T) & ":R" & M(T)) = ""
Next T
End Sub

 

[ipbr21054]

Thanks,
For T gives me a variable not found error

I believe the other two codes were a lot simplier for me.

 

[Rick Rothstein]

Thanks,
Two things ive noticed.


Here is my msgbox code
I select the cell in column N
I then see the msgbox BUT selecting NO & YES both does nothing at all & no error.
I swapped the code around but then vbNo deleted the values ?

Ive noticed that this will also work for anywhere on the sheet,can it be made specific that the selected value MUST be in column N to start with.

Thanks

Private Sub DeleteCustomer_Click()
    MsgBox "ARE YOU SURE YOU WISH TO DELETE THIS CUSTOMER", vbYesNo + vbCritical, "DELETE GRASS CUTTING CUSTOMER"
    If vbNo Then
    Exit Sub
    Else
    Range(ActiveCell, ActiveCell.Offset(0, 4)).ClearContents
    End If
End Sub

You need to use MsgBox in its function-form and capture the answer from it and then check that answer for being Yes or No (note that I used my method (Message #3) for clearing the cells)...

Private Sub DeleteCustomer_Click()
    Dim Answer As Long
    Answer = MsgBox("ARE YOU SURE YOU WISH TO DELETE THIS CUSTOMER", vbYesNo + vbCritical, "DELETE GRASS CUTTING CUSTOMER")
    If Answer = vbYes Then
        ActiveCell.Resize(1, 5).ClearContents
    Else
        Exit Sub
    End If
End Sub

 

[ipbr21054]

Thanks Rick,
Ive now got this in place & does what i require BUT can we make it clearer or clean it up to restrict the code from working ONLY for column N4 & down the page that has a value in it.
As opposed to my = 14 Then code

Private Sub DeleteCustomer_Click()
    If ActiveCell.Column = 14 Then
    Dim Answer As Long
    Answer = MsgBox("ARE YOU SURE YOU WISH TO DELETE THIS CUSTOMER", vbYesNo + vbCritical, "DELETE GRASS CUTTING CUSTOMER")
    If Answer = vbYes Then
        ActiveCell.Resize(1, 5).ClearContents
    Else
        Exit Sub
    End If
    Else
       MsgBox "YOU DIDNT SELECT A CUSTOMER FROM COLUMN N", vbExclamation, "DELETE GRASS CUTTING CUSTOMER"
    End If
End Sub

Can you also expand on your message regarding " I used my method to clear the cells"

 

[Rick Rothstein]

Ive now got this in place & d oes what i requireBUT can we make it clearer or clean it up to restrict the code from working ONLY for column N4 & down the page that has a value in it.
As opposed to my = 14 Then code

Can you also expand on your message regarding " I used my method to clear the cells"

For your first question, try changing the first If..Then statement to this...

If ActiveCell.Column = 14 And ActiveCell.Row > 3 And ActiveCell.Value <> "" Then

For your second question... see my response to your original question in Message #3.

 

[ipbr21054]

Im nearly there but not able to get it completed.

I currenty have this.

Private Sub DeleteCustomer_Click()
    If Not Application.Intersect(ActiveCell, Range("N" & Rows.Count).End(xlUp).Row Is Nothing Then
    Dim Answer As Long
    Answer = MsgBox("ARE YOU SURE YOU WISH TO DELETE THIS CUSTOMER", vbYesNo + vbCritical, "DELETE GRASS CUTTING CUSTOMER")
    If Answer = vbYes Then
        ActiveCell.Resize(1, 5).ClearContents
    Else
        Exit Sub
    End If
    Else
       MsgBox "NO CUSTOMER WAS SELECTED", vbExclamation, "DELETE GRASS CUTTING CUSTOMER"
    End If
End Sub

I can specify a range of N4:N25 but i want it to be N4 & then down the page so when i make the list longer it will only be applied to cells which have text in it.
Adding a named range of N4:N25 isnt the way to go thus having to change N25 all the time