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

Question

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.

Siddharth Rout · Accepted Answer · 2014-10-28 14:09:50Z

4

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

answered Oct 28, 2014 at 14:09

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary's Student · Accepted Answer · 2014-10-28 13:31:04Z

1

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

answered Oct 28, 2014 at 13:31

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

Gary_W · Accepted Answer · 2014-10-28 13:31:53Z

0

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.

answered Oct 28, 2014 at 13:31

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Comments

marcel89 · Accepted Answer · 2014-10-28 13:32:40Z

0

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

answered Oct 28, 2014 at 13:32

marcel89

1

Comments

mr.bjerre · Accepted Answer · 2014-10-28 13:46:42Z

0

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.

edited Oct 28, 2014 at 13:46

answered Oct 28, 2014 at 13:39

mr.bjerre

2,9382 gold badges28 silver badges45 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related