Another Delete question

[jeremiah]

I need to group records by the value in column A (employees, who can have
multiple records) and then delete all of any employees who have no records
with a date in column D.

Have given this some thought and have tried filters, loops, offsets and just
can't quite figure out the best way to do it. The groups of records will be
variable each week.

 

[Mike H]

Hi,

If I've understood correctly you want to read down column A which is a list
of names and if a name doesn't have a date in column D then delete the row.
If that's correct then right click your sheet tab, biew code and paste this
in and run it

Sub Stantial()
Dim MyRange, MyRange1 As Range
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If Not IsDate(c.Offset(, 3).Value) Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub

Mike

 

[jeremiah]

Not quite, but much closer than I had made it.
1st 4 columns from my table are below - Employee A has records in January
and February with subtotals and a grand total associated, but Employee B just
started this week, has no data from January and nothing in February until the
end of this week. I do not want his name even showing up in this table until
they have completed their first full week. I need to be able to delete all
records for Employee B out of this table so that a copy macro used later does
not automatically create a seperate worksheet for that person.
Employee A Shift 2 RFT GOALS
Employee A Shift 2 RFT 1/17/2009
Employee A Shift 2 RFT 1/31/2009
Employee A Shift 2 RFT January
Employee A Shift 2 RFT 2/7/2009
Employee A Shift 2 RFT February
Employee A Shift 2 RFT Year To Date
Employee B Shift 1 Seasonal GOALS
Employee B Shift 1 Seasonal
Employee B Shift 1 Seasonal Week Ending:
Employee B Shift 1 Seasonal
Employee B Shift 1 Seasonal February
Employee B Shift 1 Seasonal Year To Date

 

[Paul]

Jeremiah,

Looping is the way to go. BUT you work from the bottom of the list up.

For instance you have a nice list with a number of columns, starting in A1.

Sub DeleteRows()
Dim rng As Range
Dim lRows As Long
Dim lCounter As Long

Set rng = Range("A1").CurrentRegion
lRows = rng.Rows.Count

'Loop through list from the bottom.
For lCounter = lRows To 1 Step -1
If Cells(lCounter, 4).Value = "" Then
Cells(lCounter, 4).EntireRow.Delete
End If
Next
End Sub

Make sure your list has NO entirely blank rows within it to begin with.


Paul