Delete row if certain column of that row doesn't contain value

[Mikus]

I need macro that would delete enire row if column D doesn't contain value

For example I have folowing worksheet:
A B C D
1 xx ee tt gg
2
3 ss aa dd
4 ss
5 11 33 55 66

After running macro this sheet should look like this

A B C D
1 xx ee tt gg
2 ss aa dd
3 11 33 55 66

Row's 2 and 5 are deleted becouse cells in column D where empty for those
rows.
Row's 3 and 5 are shifted up

I think this would do the trick for 1st row. Now I need to add if statement
that would determine which rows to delete ?

Rows("1").Delete Shift:=xlUp

 

[Norman Jones]

Hi Mikus,

Try:
'===========================>>
Public Sub Tester03()
Dim rng As Range

Set rng = Intersect(ActiveSheet.UsedRange, Columns("D"))

On Error Resume Next
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

End Sub
'===========================>>

Or use the built in autofilter feature.

 

[Mikus]

Thank you Norman, this works flawlessly

Hi Mikus,

Try:
'===========================>>
Public Sub Tester03()
Dim rng As Range

Set rng = Intersect(ActiveSheet.UsedRange, Columns("D"))

On Error Resume Next
rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

End Sub
'===========================>>

Or use the built in autofilter feature.

 

[Djmask]

Thanks Norman!