Macro that deletes certain rows only

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 Data>Filter>Autofilter, 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
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"supamari0" <supamari0@discussions.microsoft.com> wrote in message
news:7AA53E21-B1F7-48B4-8A48-8CB0A2204149@microsoft.com...
>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 Data>Filter>Autofilter, 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
> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>
>
> "supamari0" <supamari0@discussions.microsoft.com> wrote in message
> news:7AA53E21-B1F7-48B4-8A48-8CB0A2204149@microsoft.com...
> >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 Data>Filter>Autofilter, 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
> > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
> >
> >
> > "supamari0" <supamari0@discussions.microsoft.com> wrote in message
> > news:7AA53E21-B1F7-48B4-8A48-8CB0A2204149@microsoft.com...
> > >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 Data>Filter>Autofilter, 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
> > > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
> > >
> > >
> > > "supamari0" <supamari0@discussions.microsoft.com> wrote in message
> > > news:7AA53E21-B1F7-48B4-8A48-8CB0A2204149@microsoft.com...
> > > >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 Data>Filter>Autofilter, 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
> > > > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
> > > >
> > > >
> > > > "supamari0" <supamari0@discussions.microsoft.com> wrote in message
> > > > news:7AA53E21-B1F7-48B4-8A48-8CB0A2204149@microsoft.com...
> > > > >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