How can I delete rows programmatically based on certain criteria?
 

I have a large dataset in Microsoft Excel 2003 with almost 3000 lines and
I want to delete the rows of it, which have nulls in one or more of their
columns.
e.g.
A B C D
1 2 3 4
1 4 5
2 3 4
Result : The 2nd and 3rd rows will be deleted or someway discarded.
How can I do this programmatically?

How can I delete rows programmatically based on certain criteria?
 

One way:

Public Sub DeleteLinesWithNulls()
Const nCOLS As Long = 4
Dim rDelete As Range
Dim rCell As Range

For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
If Application.CountA(rCell.Resize(1, nCOLS)) < nCOLS Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub


In article ,
nt_artagnian wrote:

I have a large dataset in Microsoft Excel 2003 with almost 3000 lines and
I want to delete the rows of it, which have nulls in one or more of their
columns.
e.g.
A B C D
1 2 3 4
1 4 5
2 3 4
Result : The 2nd and 3rd rows will be deleted or someway discarded.
How can I do this programmatically?

How can I delete rows programmatically based on certain criteria?
 

JEMcG gave you the answer as asked.

Just as an aside it may be easier to do it with autofilter.
you can add an extra col with countblank and the row you want b1:d1
then auto filter it and slectg the ones 0. delete them.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"nt_artagnian" wrote:

I have a large dataset in Microsoft Excel 2003 with almost 3000 lines and
I want to delete the rows of it, which have nulls in one or more of their
columns.
e.g.
A B C D
1 2 3 4
1 4 5
2 3 4
Result : The 2nd and 3rd rows will be deleted or someway discarded.
How can I do this programmatically?