I need to create a macro (probably can only be done through VB) that deletes
rows based on a specific criteria. It should delete all rows that don't have
a specific thing typed in it.

For example, the macro would delete all rows that don't have apple in the
cells under the A column (A1, A2, etc..)

Any way at all to do that?

If you don't want a code solution try DataFilterAutofilter, filter column
A for Apples and delete the filtered rows, either by deleting the entire
rows or clearing the data from the rows and re-sorting (blanks will drop to
bottom)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"supamari0" wrote in message
...
I need to create a macro (probably can only be done through VB) that
deletes
rows based on a specific criteria. It should delete all rows that don't
have
a specific thing typed in it.

For example, the macro would delete all rows that don't have apple in the
cells under the A column (A1, A2, etc..)

Any way at all to do that?

I have been using the autofilter but we want to set up a macro to automate
the process. Any code or help with code to get that done?

"Nick Hodge" wrote:

If you don't want a code solution try DataFilterAutofilter, filter column
A for Apples and delete the filtered rows, either by deleting the entire
rows or clearing the data from the rows and re-sorting (blanks will drop to
bottom)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"supamari0" wrote in message
...
I need to create a macro (probably can only be done through VB) that
deletes
rows based on a specific criteria. It should delete all rows that don't
have
a specific thing typed in it.

For example, the macro would delete all rows that don't have apple in the
cells under the A column (A1, A2, etc..)

Any way at all to do that?

You could automate your data|filter|autofilter and delete the visible rows.

or you could use something like:

Option explicit
sub testme()
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
if lcase(mycell.value) = lcase("apple") then
if delrng is nothing then
set delrng = mycell
else
set delrng = union(mycell, delrng)
end if
end if
next mycell

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

end sub

supamari0 wrote:

I have been using the autofilter but we want to set up a macro to automate
the process. Any code or help with code to get that done?

"Nick Hodge" wrote:

If you don't want a code solution try DataFilterAutofilter, filter column
A for Apples and delete the filtered rows, either by deleting the entire
rows or clearing the data from the rows and re-sorting (blanks will drop to
bottom)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"supamari0" wrote in message
...
I need to create a macro (probably can only be done through VB) that
deletes
rows based on a specific criteria. It should delete all rows that don't
have
a specific thing typed in it.

For example, the macro would delete all rows that don't have apple in the
cells under the A column (A1, A2, etc..)

Any way at all to do that?




--

Dave Peterson

Thanks for the coding,

I used it and set up a macro and did the following.

I typed in two cells under column A with random letters and two cells with
the word apple. Unfortunately, nothing happened when I ran the macro. I read
through your code and it looks like it should work but for some reason it
isn't doing anything.

"Dave Peterson" wrote:

You could automate your data|filter|autofilter and delete the visible rows.

or you could use something like:

Option explicit
sub testme()
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
if lcase(mycell.value) = lcase("apple") then
if delrng is nothing then
set delrng = mycell
else
set delrng = union(mycell, delrng)
end if
end if
next mycell

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

end sub

supamari0 wrote:

I have been using the autofilter but we want to set up a macro to automate
the process. Any code or help with code to get that done?

"Nick Hodge" wrote:

If you don't want a code solution try DataFilterAutofilter, filter column
A for Apples and delete the filtered rows, either by deleting the entire
rows or clearing the data from the rows and re-sorting (blanks will drop to
bottom)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"supamari0" wrote in message
...
I need to create a macro (probably can only be done through VB) that
deletes
rows based on a specific criteria. It should delete all rows that don't
have
a specific thing typed in it.

For example, the macro would delete all rows that don't have apple in the
cells under the A column (A1, A2, etc..)

Any way at all to do that?




--

Dave Peterson

Was the correct sheet active when you ran the code?

Did the cell contain only the word "apple" or did it have other text in the
cell?



supamari0 wrote:

Thanks for the coding,

I used it and set up a macro and did the following.

I typed in two cells under column A with random letters and two cells with
the word apple. Unfortunately, nothing happened when I ran the macro. I read
through your code and it looks like it should work but for some reason it
isn't doing anything.

"Dave Peterson" wrote:

You could automate your data|filter|autofilter and delete the visible rows.

or you could use something like:

Option explicit
sub testme()
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
if lcase(mycell.value) = lcase("apple") then
if delrng is nothing then
set delrng = mycell
else
set delrng = union(mycell, delrng)
end if
end if
next mycell

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

end sub

supamari0 wrote:

I have been using the autofilter but we want to set up a macro to automate
the process. Any code or help with code to get that done?

"Nick Hodge" wrote:

If you don't want a code solution try DataFilterAutofilter, filter column
A for Apples and delete the filtered rows, either by deleting the entire
rows or clearing the data from the rows and re-sorting (blanks will drop to
bottom)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"supamari0" wrote in message
...
I need to create a macro (probably can only be done through VB) that
deletes
rows based on a specific criteria. It should delete all rows that don't
have
a specific thing typed in it.

For example, the macro would delete all rows that don't have apple in the
cells under the A column (A1, A2, etc..)

Any way at all to do that?




--

Dave Peterson


--

Dave Peterson