none
Deleting rows in a macro based on a column value RRS feed

  • Question

  • I have a spreadsheet with duplicate rows. There is a hierarchy to which of the duplicate rows are to be retained. I have a logic step that returns a value of 1 for any rows to be deleted. How can I write a macro step to delete the rows that return the value of 1 in the logic column? All rows in the logic column will contain the value 1 in the formula, even if it is not displayed. I keep getting delete row commands that will delete all the rows because they all contain the 1 value inside the logic formula.
    • Edited by Weldon Culp Thursday, February 9, 2017 4:28 AM
    Thursday, February 9, 2017 4:14 AM

All replies

    1. Turn on AutoFilter
    2. Set the filter on the column with the logical 1
    3. Select entire rows for all of the visible data except column headers
    4. On Home ribbon, Select Find & Select -> Go to Special ->Visible cells only
    5. Right click over a selected area and then Delete Row
    6. Turn off AutoFilter

    Following VBA code that should do this for you. Note the comments where you will need to edit.

    Sub Macro1()
       
        Dim rngFiltered As Range
       
        With Worksheets("Sheet1")   'Edit "Sheet1" to your sheet name
            .UsedRange.Rows(1).AutoFilter
           
           'Following line edit Field:=6 to the column number with the one's
            .AutoFilter.Range.AutoFilter Field:=6, Criteria1:="1"
           
            With .AutoFilter.Range
                Set rngFiltered = .Offset(1, 0) _
                                    .Resize(.Rows.Count - 1, .Columns.Count) _
                                    .SpecialCells(xlCellTypeVisible)
            End With
           
            rngFiltered.EntireRow.Delete shift:=xlUp
            .AutoFilterMode = False
            Application.Goto .Range("A1"), scroll:=True     'Optional
        End With
    End Sub


    Regards, OssieMac

    Thursday, February 9, 2017 11:25 AM