代码之家  ›  专栏  ›  技术社区  ›  Al Grant

在列上循环以执行操作

  •  0
  • Al Grant  · 技术社区  · 7 年前

    我有一个包含数据的电子表格,标题行在第5行。我正在尝试删除某些行,调整某些行的大小,并根据标题行(第5行)名称对某些行应用筛选。

    我得到

    “运行时错误'1004'应用程序定义或对象定义错误

    在线上

    Rows(5).AutoFilter Field:=currentColumn, Criteria:="="
    

    我的完整代码示例是:

    Public Sub deleteCells()
    
    Dim currentColumn As Integer
    Dim columnHeading As String
    
    ActiveSheet.Columns("AQ").Delete
    
    For currentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1
    columnHeading = ActiveSheet.UsedRange.Cells(5, currentColumn).Value
    
    
    ' CHECK WHETHER TO KEEP COLUMN
    Select Case columnHeading
        Case "Personnel Number", "Subgroup", "Number", "Cost", "Name (repeated)", "Manager Name", "Customer Specific Status"
                ActiveSheet.Columns(currentColumn + 1).Delete
        Case "City"
        Rows(5).AutoFilter Field:=currentColumn, Criteria:="San Deigo"
        Case "Duties"
        Rows(5).AutoFilter Field:=currentColumn, Criteria:="=" ' TRYING TO EQUAL BLANKS
        Case Else
        Columns(currentColumn).ColumnWidth = 8 ' ALSO NOT SURE IF WILL TAKE A INT AS VALUE
        End Select
    Next
    'Added this line on an edit
    End Sub
    
    1 回复  |  直到 7 年前
        1
  •  1
  •   MarkyMark1000    7 年前

    Criteria:=... 
    

    Criteria1:=...