LinkBack URL
About LinkBacksActually, 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
>
Register To Reply
Bookmarks