how to delete if condition fail

[Vince]

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

 

[Harald Staff]

Sure Vince. Here are two macros. The first deletes if all three is empty, the second if one or more of them is empty. Change column
numbers to fit your needs:

Sub DeletIfAll()
Dim C1 As Long, C2 As Long, C3 As Long
Dim R As Long
Dim Killit As Boolean
C1 = 2 'column B
C2 = 5 'column E
C3 = 8 'column H

For R = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
Killit = True
If Trim$(Cells(R, C1).Value) <> "" Then Killit = False
If Trim$(Cells(R, C2).Value) <> "" Then Killit = False
If Trim$(Cells(R, C3).Value) <> "" Then Killit = False
If Killit = True Then Rows(R).Delete
Next
End Sub

Sub DeletIfOne()
Dim C1 As Long, C2 As Long, C3 As Long
Dim R As Long
Dim Killit As Boolean
C1 = 2 'column B
C2 = 5 'column E
C3 = 8 'column H

For R = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
Killit = False
If Trim$(Cells(R, C1).Value) = "" Then Killit = True
If Trim$(Cells(R, C2).Value) = "" Then Killit = True
If Trim$(Cells(R, C3).Value) = "" Then Killit = True
If Killit = True Then Rows(R).Delete
Next
End Sub

 

[Vince]

Harald,

Thank you very much for you help!!!

-Vince

Sure Vince. Here are two macros. The first deletes if all three is empty,

the second if one or more of them is empty. Change column

 

[Vince]

Hey Harald,

One followup question if i could, what you did is perfect, but is there a
way to sort by a column after the first sub listed below? The informaiton is
the two digit state code so it needs to be sort assending alphabetically.

Thanks again
Vince

 

[Harald Staff]

Sure. Just record a macro while doing it manually and the code should be ready to go.

 

[David McRitchie]

Hi Vince,
Record a macro when you sort:
Select cell G2
Ctrl+A to select all cells on the sheet, make sure that G2
is still the active cell.
Data, Sort, make sure that you check at the bottom
"My data has: header row" -- unless not true.

The following will sort the entire sheet -- presuming you want the
data on the row to follow with the data in the column you sort on.
If you zip state code in Column G, and you have a header row
the inserted code after some slight modifications would be:

Cells.Sort Key1:=Range("G2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom