Deleting rows meeting criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know that this question has been asked but I can't seem to fit the answers I have found with my problem (because of my ignorance)
I need to delete all rows that have the words Employee, Spouse or Child. Then I want to delete all empty rows. I have a macro that deletes the empty rows but if I could do it all in one, it would be better
Thank you very much
Vickie
 
Vickie, see if this will work for you

Sub Delete_Rows()
' This macro deletes all rows on the active worksheet
' that have Employee, Spouse, Child, or is blank in column A.
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "Employee" _
Or (cell.Value) = "Spouse" _
Or (cell.Value) = "Child" _
Or (cell.Value) = "" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next
On Error Resume Next
del.EntireRow.Delete

End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
Vickie Benton said:
I know that this question has been asked but I can't seem to fit the
Click to expand...
answers I have found with my problem (because of my ignorance).
I need to delete all rows that have the words Employee, Spouse or Child.
Click to expand...
Then I want to delete all empty rows. I have a macro that deletes the empty
rows but if I could do it all in one, it would be better.
 
One way:

Option Explicit
Option Compare Text
Sub Delete_Rows2()

Dim delRng As Range
Dim iRow As Long

With ActiveSheet
For iRow = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
With .Cells(iRow, "A")
If .Value = "employee" _
Or .Value = "spouse" _
Or .Value = "child" _
Or Application.CountA(.EntireRow) = 0 Then
If delRng Is Nothing Then
Set delRng = .Item(1)
Else
Set delRng = Union(.Item(1), delRng)
End If
End If
End With
Next iRow

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireRow.Delete
End If
End With

End Sub

Note the "Option compare text" at the top of the code. This means that
"employee", "emPLOYee", "EMPLOYEE" will match your criteria.

It's easier than:
if lcase(.value) = "employee" _
and so forth.

And the application.counta() will count anything that makes the cell not look
empty--including formulas that evaluate to "" (even after copy|paste
special|Values!).
 
Thanks! This is great. The only problem was that it was still looking in column A for the text, but I changed it to column C and it works fine
Thank you to Paul and Dave
Vicki
 
Back
Top