+ Reply to Thread
Results 1 to 5 of 5

delete rows based on three criterias

Hybrid View

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    Gulbarga
    MS-Off Ver
    Excel 2007
    Posts
    15

    Cool delete rows based on three criterias

    I have an excel sheet having more than 30000 rows.
    I want to delete entire rows if value in column B is either blank or zero or "Min".
    The first row contains headers.
    The sample excel file is attached herewith.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,862

    Re: delete rows based on three criterias

    Hi, try
    Sub Marreco_1059651()
    Dim startrow As Long
        'starting row number here
        startrow = 2
        ' Assuming data to check is in A Column
        Do Until startrow > Cells(Cells.Rows.Count, "A").End(xlUp).Row
            If Cells(startrow, 2).Value = "Min" Or Cells(startrow, 2).Value = 0 Or Cells(startrow, 2).Value = "" Then
                Rows(startrow).Delete
            Else
                startrow = startrow + 1
            End If
        Loop
    End Sub
    "No xadrez nem sempre a menor dist?ncia entre dois pontos ? uma linha reta" G. Kasparov.

    If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select b from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

  3. #3
    Registered User
    Join Date
    02-06-2013
    Location
    Gulbarga
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: delete rows based on three criterias

    The macro works like a charm.
    thanks a lot.

  4. #4
    Registered User
    Join Date
    02-06-2013
    Location
    Gulbarga
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: delete rows based on three criterias

    Thank you boss. you saved lot of my work.

  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,485

    Re: delete rows based on three criterias

    You can use a filter
    Sub Macro1()
        Dim Rws As Long, Rng As Range, fRng As Range
        Rws = Cells(Rows.Count, "B").End(xlUp).Row
        Set fRng = Range(Cells(1, 1), Cells(Rws, 4))
        Set Rng = Range(Cells(2, 1), Cells(Rws, 1))
        Application.ScreenUpdating = False
    
        fRng.AutoFilter Field:=2, Criteria1:=Array( _
                                             "0", "Min", "="), Operator:=xlFilterValues
        Rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        ActiveSheet.AutoFilterMode = 0
    End Sub
    Or a reverse loop
    Sub DeleteRowsReverseLoop()
        Dim lastrow As Long, r As Long
        Application.ScreenUpdating = False
    
        lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    
        For r = lastrow To 1 Step -1
            If Cells(r, 2).Value = "0" Or Cells(r, 2).Value = "Min" Or Cells(r, 2).Value = "" Then
                Rows(r).Delete
            End If
        Next r
    
    End Sub

+ 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. Hide Rows based on multiple criterias
    By Dahlia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2014, 04:47 AM
  2. [SOLVED] Delete rows based on multiple criterias, copy/paste data and formulas
    By BQuek in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2013, 01:36 PM
  3. 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
  4. Delete entire row based on criterias in range?
    By drdavidge in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2008, 02:28 PM
  5. Delete rows based on multiple criterias
    By Benson in forum Excel General
    Replies: 8
    Last Post: 11-02-2005, 06:17 PM

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