Lookup cells in one column and clear cells in another

  • Thread starter Thread starter JoshW0000
  • Start date Start date
J

JoshW0000

I need a macro to lookup the cells in column "N" that equal zero and clear
all corresponding cells in columns "M" and "J".
 
Josh
Do you mean those cells in Column N that have a zero ("0") in them or
those cells that have a mathematical value of zero? Note that a blank cell
has a mathematical value of zero. HTH Otto
 
Otto,

I mean the cells that have "0" as a mathematical value. Basically, I'm
dragging and dropping formulas in thousands of cells in columns "J" and "M"
and there is to be a space in between each group. The cells in column "N"
will be blank. I just need a macro to clear "J" and "M" where "N" is blank.

Sorry I should have been more specific.
 
Josh
This little macro will do that. I assumed your data starts in Row 2.
Sub ClearJM()
Dim LastRow As Long, LastJ As Long
Dim LastM As Long, rColN As Range
Dim i As Range, rJM As Range
Set rJM = Range("J1,M1")
LastJ = Range("J" & Rows.Count).End(xlUp).Row
LastM = Range("M" & Rows.Count).End(xlUp).Row
LastRow = Application.Max(LastJ, LastM)
Set rColN = Range("N2", Range("N" & LastRow))
For Each i In rColN
If IsEmpty(i.Value) Then
rJM.Offset(i.Row - 1).ClearContents
End If
Next i
End Sub
 
Back
Top