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

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

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

Hi Mikus,

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

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

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

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

Or use the built in autofilter feature.

---
Regards,
Norman



"Mikus" wrote in message
...
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

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

Thank you Norman, this works flawlessly

"Norman Jones" wrote:

Hi Mikus,

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

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

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

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

Or use the built in autofilter feature.

---
Regards,
Norman



"Mikus" wrote in message
...
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

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

Thanks Norman!


--
Djmask
------------------------------------------------------------------------
Djmask's Profile: http://www.excelforum.com/member.php...o&userid=24414
View this thread: http://www.excelforum.com/showthread...hreadid=396801