Delete Rows on cell content?

[Guest]

I'm starting with a work schedule spreadsheet generated from an Access DB.
Column A contains employee names, B contains a segement code, and columns C:G
contain times. It looks like this:

EmpName Code 14-Feb 15-Feb
Smith, Erin Work 10:00 10:00
Smith, Erin Break 12:00 12:00
Smith, Erin Work 12:15 12:15
Smith, Erin Lunch 14:15 14:15
Smith, Erin Work 14:30 14:30
Smith, Erin Break 16:30 16:30
Smith, Erin Work 16:45 16:45

I'm dealing with about 400 employees, each of whom have entries like this. I
would like to be able to delete all of the rows in the used range that have
"Work" in column B, leaving me with break and lunch times. Can this be
accomplished with some VBA code? Much thanks in advance!

 

[Guest]

sub delerow ()
for u = sheet1.usedrange.rows.count to 1 step - 1
if cells(u,2).value = "Work" then Rows(trim(str(u))).Entirerow.delete
next
end sub

 

[Guest]

Sweet! Thanks Ben!

sub delerow ()
for u = sheet1.usedrange.rows.count to 1 step - 1
if cells(u,2).value = "Work" then Rows(trim(str(u))).Entirerow.delete
next
end sub

 

[Guest]

Now a dumb formatting question.....know of an easy way to color fill colums
A:G based on the name? I'd like to fill the cells of every other employee
with green....so it will look something like a ledger sheet. Not every
employee will have the same number of name entries in column A though. Or is
there a way to insert a blank row between the names? Just trying to make it
easier to read. Thanks!

 

[Tom Ogilvy]

Perhaps see Chip Pearson's page on this:

http://www.cpearson.com/excel/banding.htm

 

[Guest]

Thanks. I checked out Chip's page, but unfortunately that wont work for this
application. The problem: Not every person is listed the same amount of times
in column A. Example:

Employee one is scheduled for 2 breaks and a lunch (3 row entries)
Employee two is scheduled for one break (1 row entry)
Employee three is scheduled for 1 break and one lunch (2 row entries)

So it looks like:

EmpName Code 14-Feb 15-Feb
Employee 1 Break 10:00 10:00
Employee 1 Lunch 12:00 12:00
Employee 1 Break 14:00 14:00
Employee 2 Break 11:00 11:00
Employee 3 Break 11:30 11:30
Employee 3 Lunch 13:30 13:30

I'd like to color employee 1's three entries green through colum G. Leave
employee 2 uncolored, color employee 3's entries green etc. Or, maybe insert
a blank row between each employee group. Either will make it easier to read.
Thanks!

 

[Guest]

if the employees are allready all grouped together try
Sub break()
h = 1
repe:
If h = 1 Then GoTo nexth
If Cells(h, 1).Value <> Cells(h - 1, 1).Value Then Rows(Trim(Str(h))).Insert
_ Shift:=xlDown: h = h + 1
If Cells(h, 1).Value = "" And Cells(h + 1, 1).Value = "" Then Exit Sub
nexth:
h = h + 1
If h > Sheet1.UsedRange.Rows.Count Then Exit Sub
GoTo repe
End Sub

 

[Tom Ogilvy]

I think you are only limited in your originality in identify a formula that
will produce the results you want.

The advantage with this approach is that is is dynamic.

with a macro, it is static, but that doesn't incur a calculation penatly.