根据列表删除列

本文介绍了根据列表删除列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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