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 / New Users / December 2004

Tip: Looking for answers? Try searching our database.

delete rows

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
dd - 01 Dec 2004 21:56 GMT
i would like to run a macro that does the following.
start at row 2
if L2 and m2 and n2 are all zeros then delete row
if not then go to next row and do the same check.

the macro should end once the "L" field is blank.

let me know if more info is need

thanks

--
Dave Peterson - 01 Dec 2004 23:29 GMT
One way:

Option Explicit
Sub testme()
   Dim iRow As Long
   Dim FirstRow As Long
   Dim LastRow As Long
   Dim wks As Worksheet
   Dim DelRng As Range
   
   Set wks = ActiveSheet
   With wks
       FirstRow = 2
       LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
               
       For iRow = FirstRow To LastRow
           If Application.IsNumber(.Cells(iRow, "L").Value) _
            And Application.IsNumber(.Cells(iRow, "M").Value) _
            And Application.IsNumber(.Cells(iRow, "N").Value) Then
               If Application.Max(.Cells(iRow, "L").Resize(1, 3)) = 0 _
                And Application.Min(.Cells(iRow, "L").Resize(1, 3)) = 0 Then
                   If DelRng Is Nothing Then
                       Set DelRng = .Cells(iRow, "L")
                   Else
                       Set DelRng = Union(.Cells(iRow, "L"), DelRng)
                   End If
               End If
           End If
       Next iRow
   End With
       
   If DelRng Is Nothing Then
       'do nothing
   Else
       DelRng.EntireRow.Delete
   End If
End Sub

It just checks to see if each of those cells is a number (application.isnumber
is more stringent than VBA's isnumeric).  Then if they are all numbers, it
checks to see if the max = min = 0.  If yes, then delete that row.

It starts at row 2 and finishes with the last used cell in column L (could be
slightly different than what you asked).

> i would like to run a macro that does the following.
> start at row 2
[quoted text clipped - 8 lines]
>
> --

Signature

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.