Macro that deletes certain rows only

[Guest]

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?

 

[Nick Hodge]

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
(e-mail address removed)

 

[Guest]

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?

 

[Dave Peterson]

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

 

[Guest]

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]

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?

 

[Guest]

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]

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.

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