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.

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

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

Mike maybe something like this,

Sub Delete_Data_In_E()
Dim Rng As Range
Dim FindString As String
Dim RngFound As Boolean
FindString = "Data"
Application.ScreenUpdating = False
Do
Set Rng = Range("E:E").Find(What:=FindString, LookAt:=xlWhole)
If Not Rng Is Nothing Then Rng.EntireRow.Delete: RngFound = True
Loop While Not (Rng Is Nothing)

Application.ScreenUpdating = True
If Not RngFound Then MsgBox FindString & " not found"

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 2002 & 2003

"MikeCM" wrote in message
oups.com...

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

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

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