+ Reply to Thread
Results 1 to 5 of 5

Macro help : Need a macro to delete rows that do not contain values >1 in columns O or P

Hybrid View

  1. #1
    Registered User
    Join Date
    10-19-2013
    Location
    United Kingdom
    MS-Off Ver
    MS office 2003
    Posts
    25

    Macro help : Need a macro to delete rows that do not contain values >1 in columns O or P

    So I am looking for a way to get a macro that will check if there are numbers equal to or above 1 in EITHER column O or column P.

    If both of the cells in O and P are blank, then that row needs to be deleted.

    If there is a value in column O or P that is 1 or over, then that column should be kept

    Know this should be an easy one. Any help would be appreciated.

    Thanks

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Macro help : Need a macro to delete rows that do not contain values >1 in columns O or

    sub macro_1()
    dim count
    for count = worksheetfunction.max(Range("O" & rows.count).end(xlup).row,Range("P" & rows.count).end(xlup).row) to 1 Step -1
      if Range("O" & count) = "" and Range("P" & count) = "" then rows(Count).delete
    next count
    end sub

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Macro help : Need a macro to delete rows that do not contain values >1 in columns O or

    Maybe:

    Sub ostego()
    Dim i As Long
    For i = ActiveSheet.UsedRange.Rows.count To 2 Step -1
        If Range("O" & i) = "" And Range("P" & i) = "" Then
            Rows(i).Delete
        End If
    Next i
    End Sub

  4. #4
    Registered User
    Join Date
    07-17-2012
    Location
    Delhi
    MS-Off Ver
    Excel 2007
    Posts
    61

    Re: Macro help : Need a macro to delete rows that do not contain values >1 in columns O or

    Give a format..

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,649

    Re: Macro help : Need a macro to delete rows that do not contain values >1 in columns O or

    May be this.......
    Sub deleterows()
    Dim lr As Long
    lr = Cells(Rows.Count, "O").End(xlUp).Row
    For i = lr To 2 Step -1
        If Cells(i, "O").Value >= 1 Or Cells(i, "O").Offset(0, 1).Value >= 1 Then
        'do nothing
        Else
        Cells(i, "O").EntireRow.Delete
        End If
    Next i
    End Sub
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to delete rows and columns
    By siroco79 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-04-2013, 07:37 AM
  2. Macro to delete certain columns and delete rows based on time in another column
    By beepbeep27 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-12-2012, 11:47 AM
  3. Macro to Delete Values if in both columns
    By Enemee in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-15-2011, 08:10 AM
  4. Macro to delete certain rows and create columns
    By excel77 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-14-2010, 10:06 AM
  5. Macro to delete entire rows based values in columns
    By shamade2107 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-13-2009, 11:17 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1