Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

Can I conditionally delete row in Excel? (if B3=0 delete row B)

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Badger - 22 Jan 2006 15:50 GMT
I am trying to delete rows based on conditional statements.  For example,
delete row B if cell B3(budget balance) is 0.  I am having trouble figuring
this out.  Can I incorporate a macro that deletes a row into an "if"
statement?   Thanks.
Dave Peterson - 22 Jan 2006 16:00 GMT
Do you need a macro or can you do it manually?

If manually is ok:

I'd use another column of Cells:

=if(b2=0,"deleteme","keepme")
and drag down.

Then apply data|filter|autofilter to that helper column.  And show the Deleteme
rows.

And delete those visible rows.

Remove the filter and delete that helper column.

> I am trying to delete rows based on conditional statements.  For example,
> delete row B if cell B3(budget balance) is 0.  I am having trouble figuring
> this out.  Can I incorporate a macro that deletes a row into an "if"
> statement?   Thanks.

Signature

Dave Peterson

Badger - 22 Jan 2006 17:03 GMT
Thanks.  I need it to be automated so I think a macro is my only choice
unless I can somehow incorporate a conditional delete row function into a
cell formula.

> Do you need a macro or can you do it manually?
>
[quoted text clipped - 16 lines]
> > this out.  Can I incorporate a macro that deletes a row into an "if"
> > statement?   Thanks.
Dave Peterson - 22 Jan 2006 17:18 GMT
Maybe something like:

Option Explicit
Sub testme()

   Dim rng As Range
   Dim wks As Worksheet
   Dim myCell As Range
   Dim DelRng As Range
   
   Set wks = Worksheets("Sheet1")
   
   With wks
       Set rng = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
   End With
   
   For Each myCell In rng.Cells
       If IsEmpty(myCell) = False Then
           If myCell.Value = 0 Then
               If DelRng Is Nothing Then
                   Set DelRng = myCell
               Else
                   Set DelRng = Union(myCell, DelRng)
               End If
           End If
       End If
   Next myCell
   
   If DelRng Is Nothing Then
       'do nothing
   Else
       DelRng.Select
       'or (after testing!)
       'DelRng.EntireRow.Delete
   End If
   
End Sub

> Thanks.  I need it to be automated so I think a macro is my only choice
> unless I can somehow incorporate a conditional delete row function into a
[quoted text clipped - 24 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Badger - 23 Jan 2006 01:21 GMT
Thank you so much!

> Maybe something like:
>
[quoted text clipped - 62 lines]
> > >
> > > Dave Peterson
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.