B
B
B_M2018-06-14 20:26:22
Visual Basic
B_M, 2018-06-14 20:26:22

How to clear values ​​of other cells when clearing a cell in ms excel?

There is a table with values. It is necessary, when I press delete in cell A1, to automatically clear the values ​​in cells H1 and Q1. Please tell me how to implement this using VBA.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
X
x67, 2018-06-15
@B_M

Yes, excel has events. Tailor the Elvis Code to suit your needs. The condition should be like if selection.column=1 and selection.value="" then range(the range itself here).clearcontents
And you don't need to constantly select (select) the range for deletion - this is a completely unnecessary gesture.
It would also be nice to check the selection for the number of cells.

Private Sub Worksheet_Change(ByVal Target As Range)
For Each t In Target
    If t.Column = 1 And t.Value = "" Then
        Range("H" & t.Row).ClearContents
        Range("Q" & t.Row).ClearContents
    End If
Next t
End Sub

E
Elvis, 2018-06-14
@Dr_Elvis

And basically on delete?
You can do this:

Sub del()
    Dim row As String
    row = ActiveCell.row
    Range("H" & row).Select
    Selection.ClearContents
    Range("Q" & row).Select
    Selection.ClearContents
    Range("A" & row).Select
    Selection.ClearContents
End Sub

After that, we assign a macro, for example, to Ctrl + k and press this combination instead of delete. In the macro, I deliberately first cleared the cell in the "H" and "Q" columns, and then only in the "A" column. This is so that after the macro is executed, the cursor is in the "A" column.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question