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:D"))

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

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

Or use the built in autofilter feature.

---
Regards,
Norman



"Mikus" <[email protected]> wrote in message
news:[email protected]...
>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
>
>

[Mikus]

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.Delete
> On Error GoTo 0
>
> End Sub
> '===========================>>
>
> Or use the built in autofilter feature.
>
> ---
> Regards,
> Norman
>
>
>
> "Mikus" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
> >
>
>
>

[Djmask]

Thanks Norman!