基于列值删除宏中的行 [英] Deleting rows in a macro based on a column value

问题描述

我有一个包含重复行的电子表格。有一个层次结构要保留哪些重复行。我有一个逻辑步骤，为要删除的任何行返回值1。如何编写宏步骤来删除逻辑列中返回
值为1的行？逻辑列中的所有行都将在公式中包含值1，即使它未显示。我一直在删除所有行的删除行命令，因为它们都包含逻辑公式中的1值。

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.

推荐答案

1. 启用AutoFilter
2. 使用逻辑1
3. 在列上设置过滤器为列标题以外的所有可见数据选择整行
4. 在主页功能区上，选择查找&选择 - >转到特别 - >可见细胞仅
5. 右键单击在选择的区域，然后删除行
6. 关闭自动筛选

关注应该为您执行此操作的VBA代码。请注意您需要修改的评论。

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

Sub Macro1（）

   

    Dim rngFiltered As Range

   

   使用工作表（"Sheet1"）   '编辑" Sheet1"到您的工作表名称

        .UsedRange.Rows（1）.AutoFilter

       

      &的 '继行编辑字段：= 6至与所述一个的

        .AutoFilter.Range.AutoFilter字段：= 6，标准1：=" 1 QUOT;

       

       使用.AutoFilter.Range

           设置rngFiltered = .Offset（1,0）_

               &NBSP ;                .Resize（.Rows.Count - 1，.Columns.Count）_

                                 .SpecialCells（xlCellTypeVisible）

       结束与$
      

        rngFiltered.EntireRow.Delete shift：= xlUp

        .AutoFilterMode = False

        Application.Goto .Range（" A1"），scroll：= True   
 '可选

   结束于$
结束子

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

这篇关于基于列值删除宏中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！