Delete Rows Based on Values in Multipul Column

[mayuko]

I need to make a macro to do this...

"If there are values in column G, but nothing in column L and N, then delete rows"

It would be appreciated if anyone could help me with this.

Thank you

 

[AlphaFrog]

Sub Del_Rows()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = Range("G" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Range("G" & i) <> "" And Range("L" & i) = "" And Range("N" & i) = "" Then Rows(i).Delete
    Next i
    Application.ScreenUpdating = True
End Sub

 

[p45cal]

Sub blah()
With ActiveSheet
    toprow = .UsedRange.Row
    lastrow = toprow + .UsedRange.Rows.Count - 1
    For i = lastrow To toprow Step -1
        If (Not IsEmpty(.Cells(i, "G"))) And IsEmpty(.Cells(i, "L")) And IsEmpty(.Cells(i, "N")) Then .Rows(i).Delete
    Next i
End With
End Sub

 

[mayuko]

Thank you very much, AlphaFrog and p45cal

I am so sorry. Either of them worked. Both of them left rows that have empty cells in L and N...

 

[AlphaFrog]

Did you want to delete rows if L OR N was blank?

If yes, then change this...

If Range("G" & i) <> "" And Range("L" & i) = "" And Range("N" & i) = "" Then Rows(i).Delete

To this...

If Range("G" & i) <> "" And (Range("L" & i) = "" Or Range("N" & i) = "") Then Rows(i).Delete

 

[mayuko]

Thanks AlphaFrog. I am sure it is And... I ran it after I changed it to Or, then it deleted ones I wanted to leave

 

[AlphaFrog]

Maybe try this...

If Range("G" & i) <> "" And (Trim(Range("L" & i)) = "" And Trim(Range("N" & i)) = "") Then Rows(i).Delete

If the so-called empty cells in L and N contained a space, they may "look" empty but are not really empty. The Trim function would effectively erase the space in those cases.

 

[mayuko]

Thank you so much! It worked perfect