find <> empty cell in range, delete entire row

This is my first post here, so welcome Everyone I'm just starting the adventure with programming and hope to learn more thanks to your experience (and count on your sense of humor and comprehension in case of lame questions ).

My today's problem is as follows: I'm trying to write a macro that searches for not empty cell in given column and deletes entire row once the condition is fulfilled.
So as to limit the range (not to search in entire column) I'm trying to specify last row in the table. However, the macro (see code below) is not working correctly - no error occurs but it still is not working as expected (deletes some rows, empty or not).

If you could look into this and help me to understand where's en error in my way of thinking if would be great.

Dim LastRow As Integer
Dim LastCol As Integer
Dim Cell As Range

' define last cells in row and column

LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
LastCol = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

' search for not empty cells in column D (D2:LastRow); if cell not empty, delete entire row

For Each Cell In Range(Cells(2, 4), Cells(LastRow, 4))
If Cell.Value <> "" Then
ActiveCell.EntireRow.Select
Selection.Delete

End If
Next Cell

Many thanks in advance!

Try this

Cell.EntireRow.Delete

instead of this:

ActiveCell.EntireRow.Select
Selection.Delete

Regards

Hi,

You are effectively looping through your range of cells but your active cell never changes, hence whenever the loop If statement is true you are merely deleting the active cell row moving everything up.

However looping through cells and then taking some action is essentially a time consuming process, particularly with large ranges since the VBE needs to keep jumping backwards and forwards to the Excel application.
If there is some standard Excel functionality that will achieve the task then you should always use that. In this case it would be better to use Data Filter to filter the column for the values you want to identify and then delete all the filtered rows in one go.

You can of course write a macro to automate the filter and delete.

Regards