delete row?
 

need some help on this one.

Is there a way to check to see if there is a value or not null for three
cells in a row and if there is not, then delete the row?

Example:

I have sheets of customer information where each row is a different customer
(name, address, ect..), but if they do not have a contact number (home,
work, cell) i would like to delete them.

I'm pretty sure that this can be done via macro, but don't know how. Any and
all help is greatly appreciated!

Thanks
Vince

delete row?
 

Try this

This will check the first 10 rows on the active worksheet and
check if there are values in A:C

Change to your situation

Sub test()
Dim a As Long
For a = 10 To 1 Step -1
With Application.WorksheetFunction
If .CountA(Range("A" & a & ":C" & a)) = 0 Then
Rows(a).Delete
End If
End With
Next a
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Vince" wrote in message ...
need some help on this one.

Is there a way to check to see if there is a value or not null for three
cells in a row and if there is not, then delete the row?

Example:

I have sheets of customer information where each row is a different customer
(name, address, ect..), but if they do not have a contact number (home,
work, cell) i would like to delete them.

I'm pretty sure that this can be done via macro, but don't know how. Any and
all help is greatly appreciated!

Thanks
Vince

delete row?
 

See I misinterpreted your situation - disregard.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
Assume your data is contiguous with headers in row 1 and each "customer
record" consisting of 3 rows with no dividers/blank lines separating a
customer and the check is made in column A. If any cell in a 3 row group
in column A is blank, the 3 row group is deleted. In the example, your 3
row groups start in A2 and end in A31.

Sub Tester1()
Dim rng As Range, rng1 As Range
Dim cell As Range, rng2 As Range
Set rng = Range("A2:A31")
For Each cell In rng
If (cell.Row - 2) Mod 3 = 0 Then
Set rng1 = cell.Resize(3, 1)
If Application.CountA(rng1) < 3 Then
If rng2 Is Nothing Then
Set rng2 = rng1
Else
Set rng2 = Union(rng2, rng1)
End If
End If
End If
Next
If Not rng2 Is Nothing Then
rng2.EntireRow.Delete
End If


End Sub

That is about the best guess I can make on the limited information you
give.
It is hard to test an unspecified cell.

--
Regards,
Tom Ogilvy



"Vince" wrote in message
...
need some help on this one.

Is there a way to check to see if there is a value or not null for three
cells in a row and if there is not, then delete the row?

Example:

I have sheets of customer information where each row is a different
customer
(name, address, ect..), but if they do not have a contact number (home,
work, cell) i would like to delete them.

I'm pretty sure that this can be done via macro, but don't know how. Any
and
all help is greatly appreciated!

Thanks
Vince