Delete Rows on cell content?
 

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!

Delete Rows on cell content?
 

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

"fpd833" wrote:

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!

Delete Rows on cell content?
 

Sweet! Thanks Ben!

"ben" wrote:

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

"fpd833" wrote:

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!

Delete Rows on cell content?
 

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!

"fpd833" wrote:

Sweet! Thanks Ben!

"ben" wrote:

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

"fpd833" wrote:

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!

Delete Rows on cell content?
 

Perhaps see Chip Pearson's page on this:

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

--
Regards,
Tom Ogilvy

"fpd833" wrote in message
...
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!

"fpd833" wrote:

Sweet! Thanks Ben!

"ben" wrote:

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

"fpd833" wrote:

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!