CODEWITHSUNDEEP
excelrangedelete-rowvba
user2980029
user2980029

Reputation: 23

VBA code to delete a row based on a non empty cell in a column

I am running a report of employees who charge time to different codes. The report gives me the following columns:

Emp# / Emp Name / Rate / TermDate

If the employee has left, then there is a value in the TermDate column.

Because the value in the cell could be any date, I want to write a macro that will search the list and delete any row in which the value in the fourth column is NOT blank.

I've found several examples of how to delete a row based on blank cells, or based on certain values, but none on a non-blank value.

Each report will have a different number of rows as well so I need help selecting the range and deleting rows in the range based on the value of the final column.

Upvotes: 1

Views: 11602

Answers (5)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Fastest way. Use .Autofilter. No need to loop...

Sub Sample()
    Dim LRow As Long
    Dim delRange As Range

    With ThisWorkbook.Sheets("Sheet1")
        '~~> Remove any filters
        .AutoFilterMode = False

        LRow = .Range("D" & .Rows.Count).End(xlUp).Row

        With .Range("A1:D" & LRow)
            .AutoFilter Field:=4, Criteria1:="<>"
            Set delRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With

    If Not delRange Is Nothing Then delRange.Delete
End Sub

Upvotes: 4

mr.bjerre
mr.bjerre

Reputation: 2898

If I understand correctly, then you want something like this?

Sub DeleteNonEmptyRows()

  Dim row As Integer, N As Integer

  N = Cells(Rows.Count, "D").End(xlUp).Row

  For row = N To 2 Step -1
      If Not IsEmpty(Range("D" & row)) Range("A" & row).EntireRow.Delete
  Next row

End Sub

It is assumed that TermDate is in column D.

Upvotes: 0

marcel89
marcel89

Reputation: 1

My solution would be:

Dim lng_LastRow As Long
Dim lng_i As Long

lng_LastRow = Range("A1").End(xlDown).Row

For lng_i = 1 To lng_LastRow

    If Len(Trim(Cells(lng_i, 4).Value)) > 0 Then
        Rows(lng_i).Delete
        lng_i = lng_i - 1
    End If


Next lng_i

Upvotes: 0

Gary_W
Gary_W

Reputation: 10360

Consider using a filter on the data instead. You didn't mention what version of Excel, but in 2010 go to the data tab, select the termdate column, then click on "filter".

Notice a little dropdown arrow appears in the first cell of the column. Click on that, then deselect all but the "blanks" selection. Wha-la.

That way the data is still there for history if you need it.

Oh and after applying the filter, a little funnel icon will remain in the first cell of the column to show it's a filtered column.

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

This assumes that the column to check is column D

Sub RowKiller()
    Dim col As String, N As Long, i As Long
    col = "D"
    N = Cells(Rows.Count, col).End(xlUp).Row
    For i = N To 1 Step -1
        If Cells(i, col).Value <> "" Then
            Cells(i, col).EntireRow.Delete
        End If
    Next i
End Sub

Upvotes: 1

Related Questions