CODEWITHSUNDEEP
excelvba
Sami Glass
Sami Glass

Reputation: 25

Deleting Rows Based on Multiple Cell Values

I'm trying to delete rows that are empty in Column A and not empty in Column B in an Excel sheet.

I need to keep the rows that have values in A, however I also need to keep the rows that have nothing in them (they are my spacers).

I tried this:

Sub DoStuffIfNotEmpty()
    If Not IsEmpty(Colmuns("B").Value) Then
        ActiveCell.Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
    End If
End Sub

Which deleted my spacers.

I tried this:

Sub QuickCull()
    On Error Resume Next
    Columns("A").Columns("B").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

Which deleted one of the rows with this criteria.

I tried this:

Sub delete_Me()
Dim LastRow As Long, x As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For x = LastRow To 1 Step -1
    If Not IsEmpty(Colmuns("B").Value) And _
    Columns("A").Value Then
        Rows(x).Delete
    End If
Next
End Sub

Which does nothing.

Upvotes: 2

Views: 3689

Answers (3)

zack.lore
zack.lore

Reputation: 527

Try something like this:

Sub removeRows()

Dim LastRow As Long
Dim rowNum As Integer
LastRow = Cells(Rows.Count, "B").End(xlUp).Row

For rowNum = LastRow To 1 Step -1
    If Range("B" & rowNum).Value <> "" And Range("A" & rowNum).Value = "" Then
        Rows(rowNum).Delete
    End If
Next rowNum

End Sub

As was pointed out by @findwindow and @Jeeped, the loop should run from the bottom row to the top. My mistake. Also I adjusted the LastRow to be counted using Column "B".

Upvotes: 3

user4039065
user4039065

Reputation:

Use the Range.AutoFilter Method. Filter for blanks on column A and non-blanks on column B. After checking to see if there are rows to delete, delete them.

Sub del_blankA_valueB()
    With Worksheets("Sheet1")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .UsedRange.Cells
            .AutoFilter field:=1, Criteria1:="="
            .AutoFilter field:=2, Criteria1:="<>"
            With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                If CBool(Application.CountA(.Columns(2))) Then
                    .Cells.EntireRow.Delete
                End If
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

Upvotes: 2

Davesexcel
Davesexcel

Reputation: 6984

Possibly using Count or counta as well

Sub Button1_Click()
    Dim LstRw As Long, Rng As Range
    LstRw = Cells(Rows.Count, "A").End(xlUp).Row


    For x = LstRw To 1 Step -1
        If Application.WorksheetFunction.Count(Range(Cells(x, 1), Cells(x, 2))) = 0 Then Rows(x).Delete
    Next

End Sub

Upvotes: 2

Related Questions