Dave/Paul - thanks to both of you for your thoughts, I've got it to
work, plus also learned something as a result. Much appreciated!

Mike


Dave Peterson wrote:
First, comparing a lcase(mycell.value) to "Data" (with the upper case D) will
cause unintended problems. That comparison will never be true.

Second, I thought you wanted to delete the rows with Data in them. You're
keeping those rows and deleting the rest (under the case Else portion)

Third, the code looks at column A, not column E.

How about:

Option explicit
sub delete_()
dim myCell as range
dim myRng as range
dim delRng as range

with activesheet
set myrng = .range("e2", .cells(.rows.count,"e").end(xlup))
end with

for each mycell in myrng.cells
select case lcase(mycell.value)
case lcase("Data")
if delrng is nothing then
set delrng = mycell
else
set delrng = union(mycell, delrng)
end if
end select
next mycell

if delrng is nothing then
'do nothing
else
delrng.entirerow.delete
end if

end sub

ps. It's usually better to copy from the VBE and paste into your message. It's
just too easy to inject errors other ways (retyping???).



MikeCM wrote:

Thanks for the suggestion. Actually I was thinking more along the lines
of somethings such as (which I got from a similar discussion a while
back):

Option explicit
sub delete_()
dim myCell as range
dim myRng as range
dim delRng as range

with activesheet
set myrng = .range("a2", .cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
select case lcase(mycell.value)
case "Data"
'do nothing
case else
if delrng is nothing then
set delrng = mycell
else
set delrng = union(mycell, delrng)
end if
end select
next mycell

if delrng is nothing then
'do nothing
else
delrng.entirerow.delete
end if

end sub

The problem is that this deletes every row. I can't work out why
though. Any thoughts?

Mike

Dave Peterson wrote:
Record a macro when you apply data|filter|autofilter to column E. Then filter
to show only Data. Then delete those visible rows. Remove the autofilter and
stop recording the macro.

MikeCM wrote:

I am looking for a macro that will automatically delete all rows in a
worksheet where the value in column E is "Data". Any suggestions/help?
Thanks.

--

Dave Peterson

--

Dave Peterson