vb code for excel

jamesmetcalf74
jamesmetcalf74 used Ask the Experts™
on
can someone write this for me to put into a macro in excel.

there are two columns in play.   j & k

i need the value in the cells in column k to be deleted if there is a blank in the cell right beside it in column j
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Put this code in the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("J" & Target.Row)) Is Nothing Then
        Range("K" & Target.Row).Value = ""
    End If
End Sub

Open in new window

For a once-off operation, use this...

Sub DeleteAdjacentBlanks()
    Range("J:J").SpecialCells(xlCellTypeBlanks).Offset(0, 1).ClearContents
End Sub

Open in new window

You can call this macro within your existing macro or add it in your existing macro.

Sub Test()

With Intersect(Columns("j:j"), ActiveSheet.UsedRange)
    If WorksheetFunction.CountBlank(.Cells) > 0 Then
        .SpecialCells(xlCellTypeBlanks).Offset(, 1).ClearContents
    End If
End With

End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial