Clear the contents of columns B to F if cell A is empty

excel, vba

Solution

You are using a Worksheet_Change event and you iterating through 70 rows each time something changes.. this is a bad approach for this kind of problem and that's why there is a delay.

Instead, try

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Long

    If Target.Column = 1 Then
        If IsEmpty(Cells(Target.Row, 1)) Then
               Range("B" & Target.Row & ":F" & Target.Row).ClearContents
        End If
    End If
End Sub

this will only clear the cells if you remove a value from column A => when cell in column A is empty

Problem

I have a worksheet with values depending on Cell A. If a row in column A contains a value then cells from Columns B through H will be changed accordingly. If Cell of Column A is empty I want to reset the cells from columns D through F. I wrote down the following VBA Code ``` Private Sub Worksheet_Change(ByVal Target As Range) Dim n As Integer For n = 5 To 75 Application.EnableEvents = False If VarType(Cells(n, 1)) = vbEmpty Then Cells(n, 4).ClearContents Cells(n, 5).ClearContents Cells(n, 6).ClearContents Application.EnableEvents = True End If Next n End Sub ``` The "FOR" Loop is annoying, and making the Excel to pause for 1 second or more after any entry to any Cell, can anyone help me correct the above code to do what I need to do without the "FOR" loop.

Original source