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

Actually, I found this code that I believe you wrote earlier and this worked
for me. But I know have a slightly different question but I think it should
be easy enough. I need it so that it won't delete rows that have either
"apple" or "banana." In other words, how do you make it so that the macro
does not delete a row if it has either one word or another?

Thanks in advance!

Option Explicit
Sub DeleteRowifNoText()
Dim rng As Range
Dim whatToFind As String
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

whatToFind = "apple"
With ActiveSheet
FirstRow = 1 'no headers?
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
With .Rows(iRow)
Set rng = .Find(what:=whatToFind, after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByColumns, _
searchdirection:=xlNext, MatchCase:=False)
End With
If rng Is Nothing Then
..Rows(iRow).Delete
End If
Next iRow
End With
End Sub

"Dave Peterson" wrote:

> 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
>

I would go back to the other code and modify it:

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
select case lcase(mycell.value)
case "apple","banana"
'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

This still assumes that there are no other characters than "apple" and "banana"
in those cells.



supamari0 wrote:
>
> Actually, I found this code that I believe you wrote earlier and this worked
> for me. But I know have a slightly different question but I think it should
> be easy enough. I need it so that it won't delete rows that have either
> "apple" or "banana." In other words, how do you make it so that the macro
> does not delete a row if it has either one word or another?
>
> Thanks in advance!
>
> Option Explicit
> Sub DeleteRowifNoText()
> Dim rng As Range
> Dim whatToFind As String
> Dim FirstRow As Long
> Dim LastRow As Long
> Dim iRow As Long
>
> whatToFind = "apple"
> With ActiveSheet
> FirstRow = 1 'no headers?
> LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>
> For iRow = LastRow To FirstRow Step -1
> With .Rows(iRow)
> Set rng = .Find(what:=whatToFind, after:=.Cells(1), _
> LookIn:=xlFormulas, lookat:=xlWhole, _
> searchorder:=xlByColumns, _
> searchdirection:=xlNext, MatchCase:=False)
> End With
> If rng Is Nothing Then
> .Rows(iRow).Delete
> End If
> Next iRow
> End With
> End Sub
>
> "Dave Peterson" wrote:
>
> > 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
> >

--

Dave Peterson