根据列表删除列 [英] Delete columns based on a list

问题描述

我有一个宏，如果标题包含组时间，则删除列：

  Sub remove_columns（）
对于i = ActiveSheet.Columns.Count到1步-1 
如果InStr（1，Cells（1，i），Group time：）然后列（i）.EntireColumn.Delete 
下一个我
结束Sub 
  

我有其他列我需要删除总时间 最后一页，开始语言...大约10个。

我可以创建10个版本的上述宏，但有一种方法只有一个具有名称列表并删除这些列的宏

谢谢

解决方案

pre> Sub remove_columns（）

Dim rngFound As Range
Dim rngDel As Range
Dim arrColumnNames（）As Variant
Dim varName As Variant
Dim strFirst As String

arrColumnNames = Array（Group time：，Total time，Last page，Start language）

对于每个varName在arrColumnNames
设置rngFound =行（1）.Find（varName，Cells（1，Columns.Count），xlValues，xlPart）
如果没有rngFound是没有
strFirst = rngFound.Address
Do
如果rngDel不是，然后设置rngDel = rngFound Else Set rngDel = Union（rngDel，rngFound）
设置rngFound = Rows（1）.Find（varName，rngFound，xlValues，xlPart）
循环while rngFound.Address<> strFirst
End If
下一个varName

如果没有rngDel是没有，那么rngDel.EntireColumn.Delete

设置rngFound =没有
设置rngDel =没有
擦除arrColumnNames

End Sub


I have a Macro that deletes a column if the header contains "Group time:"

Sub remove_columns()
    For i = ActiveSheet.Columns.Count To 1 Step -1
        If InStr(1, Cells(1, i), "Group time:") Then Columns(i).EntireColumn.Delete
    Next i
End Sub

I have other columns I need to delete "Total time", "Last page", "Start language" ... about 10 in all.

I could create 10 versions of the above macro but is there a way to have only one macro that has a list of names and deletes those columns

Thanks

解决方案

Sub remove_columns()

    Dim rngFound As Range
    Dim rngDel As Range
    Dim arrColumnNames() As Variant
    Dim varName As Variant
    Dim strFirst As String

    arrColumnNames = Array("Group time:", "Total time", "Last page", "Start language")

    For Each varName In arrColumnNames
        Set rngFound = Rows(1).Find(varName, Cells(1, Columns.Count), xlValues, xlPart)
        If Not rngFound Is Nothing Then
            strFirst = rngFound.Address
            Do
                If rngDel Is Nothing Then Set rngDel = rngFound Else Set rngDel = Union(rngDel, rngFound)
                Set rngFound = Rows(1).Find(varName, rngFound, xlValues, xlPart)
            Loop While rngFound.Address <> strFirst
        End If
    Next varName

    If Not rngDel Is Nothing Then rngDel.EntireColumn.Delete

    Set rngFound = Nothing
    Set rngDel = Nothing
    Erase arrColumnNames

End Sub

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