VBA code to delete rows based on value

[Sari]

Hi all,

I hope you can help me with the following:
I have a table with 2 columns (A&B) and headers. In column A values are contained in all cells and Column B has empty cells and cells with data.
So actually I need a loop with an if clause like:

If range("A").value = true AND range("B").value = true Then
EntireRow.Delete

But unfortunately I'm very bad in the looping function, so I have no clue how to get this solved.
If I missed to mention important facts, my apologies, please let me know.

Greets,
Sari

[mana]

Please post your code.

[Sari]

This is my code, but it's not doing what I was planned to:
In the first part of the code I copy the data from another sheet as the original (Kit Inventory List) may not be changed. The purpose of the second sheet (Empty Loc Che is to only show the rows which have missing data in column B.

Private Sub CommandButton2_Click()
'Export empty locators
ThisWorkbook.Worksheets("Empty Loc Check").Cells.Clear
Dim OutputLoc As Range
Set OutputLoc = ThisWorkbook.Worksheets("Empty Loc Check").Range("A:B")
Sheets("Kit Inventory List").Range("A:B").Copy OutputLoc
Sheets("Empty Loc Check").Rows("1").EntireRow.Delete

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    With Worksheets("Empty Loc Check")
        .Select
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        For Lrow = Lastrow To Firstrow Step -1
            With .Cells(Lrow, "A")
                If Not IsError(.Value) Then
                    If .Value = NotEmpty Then .EntireRow.Delete
                End If
            End With
        Next Lrow
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With
End Sub

[mana]

What do you want to delete?
empty cells or error cells?

[mana]

??

Option Explicit


Sub test()
    Dim c As Range
    
    For Each c In Worksheets("Empty Loc Check").UsedRange.Columns(1).Cells
        If IsEmpty(c) Then
            If IsEmpty(c.Offset(, 1)) Then
                c.EntireRow.Delete
            End If
        End If
    Next
    
End Sub

Ummm...
why i can't reply private message

[Sari]

Hi mana,

empty cells. I tried your code, but unfortunately the rows with value in A and B are still populated....
Is there a possibility to see where the code makes the mistake as I don't receive any error message
Thank you very much for your help!

[mana]

Sub test2()

    On Error Resume Next
    Worksheets("Empty Loc Check").Columns("A:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
    
End Sub

[mdmackillop]

Sub Test()
With Worksheets("Empty Loc Check")
Intersect(.UsedRange, .Columns(2).SpecialCells(2)).EntireRow.Delete
End With
End Sub