vba 如何使用VBA删除特定列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26580082/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete specific columns with VBA?
提问by Zaynaib Giwa
I am trying to delete multiple columns in VBA for Excel. I downloaded data from the Illinois Statistical analysis center about drug arrest. http://www.icjia.org/public/sac/index.cfm?metasection=forms&metapage=rawMetadata&k=170
我正在尝试删除 VBA for Excel 中的多列。我从伊利诺伊州统计分析中心下载了有关毒品逮捕的数据。http://www.icjia.org/public/sac/index.cfm?metasection=forms&metapage=rawMetadata&k=170
Each of the columns I want to delete are 3 columns apart from each other.
我要删除的每一列彼此相距 3 列。
For example:
例如：
Adams County Illinois Champaign County Illinois Estimate |percent | Percent Margin of Error |Estimate Margin| Estimate| Percent | Percent Margin of Error
亚当斯县伊利诺伊州香槟县伊利诺伊州估计|百分比| 误差百分比|估计余量| 估计| 百分比 | 误差幅度百分比
D|E|F|G|H|I|J
D|E|F|G|H|I|J
I just want to delete all the columns the say Percent Margin of Error
我只想删除所有列，例如误差百分比
Here is my macro:
这是我的宏：
Sub deleteCol()
Columns("H,J").Delete
End Sub
I keep getting an error:
我不断收到错误消息：
Run-time 13: type mismatch
运行时 13：类型不匹配
Any suggestions?
有什么建议？
采纳答案by BobbitWormJoe
You say you want to delete any column with the title "Percent Margin of Error" so let's try to make this dynamic instead of naming columns directly.
您说要删除标题为“误差百分比”的任何列，因此让我们尝试使其动态化，而不是直接命名列。
Sub deleteCol()
On Error Resume Next
Dim wbCurrent As Workbook
Dim wsCurrent As Worksheet
Dim nLastCol, i As Integer
Set wbCurrent = ActiveWorkbook
Set wsCurrent = wbCurrent.ActiveSheet
'This next variable will get the column number of the very last column that has data in it, so we can use it in a loop later
nLastCol = wsCurrent.Cells.Find("*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
'This loop will go through each column header and delete the column if the header contains "Percent Margin of Error"
For i = nLastCol To 1 Step -1
If InStr(1, wsCurrent.Cells(1, i).Value, "Percent Margin of Error", vbTextCompare) > 0 Then
wsCurrent.Columns(i).Delete Shift:=xlShiftToLeft
End If
Next i
End Sub
With this you won't need to worry about where you data is pasted/imported to, as long as the column headers are in the first row.
有了这个，只要列标题位于第一行，您就无需担心数据粘贴/导入的位置。
EDIT: And if your headers aren'tin the first row, it would be a really simple change. In this part of the code: If InStr(1, wsCurrent.Cells(1, i).Value, "Percent Margin of Error", vbTextCompare)change the "1" in Cells(1, i)to whatever row your headers are in.
编辑：如果您的标题不在第一行，那将是一个非常简单的更改。在这部分代码中：If InStr(1, wsCurrent.Cells(1, i).Value, "Percent Margin of Error", vbTextCompare)将“1”更改为Cells(1, i)标题所在的任何行。
EDIT 2: Changed the Forsection of the code to account for completely empty columns.
编辑 2：更改了For代码部分以说明完全空的列。
回答by peege
You were just missing the second half of the column statement telling it to remove the entire column, since most normal Ranges start with a Column Letter, it was looking for a number and didn't get one. The ":" gets the whole column, or row.
您只是错过了告诉它删除整个列的列语句的后半部分，因为大多数正常范围以列字母开头，它正在寻找一个数字但没有得到一个数字。":" 获取整列或整行。
I think what you were looking for in your Range was this:
我认为您在 Range 中寻找的是这样的：
Range("C:C,F:F,I:I,L:L,O:O,R:R").Delete
Just change the column letters to match your needs.
只需更改列字母即可满足您的需求。
回答by Arun
To answer the question How to delete specific columns in vba for excel. I use Array as below.
回答如何在 vba for excel 中删除特定列的问题。我使用数组如下。
sub del_col()
dim myarray as variant
dim i as integer
myarray = Array(10, 9, 8)'Descending to Ascending
For i = LBound(myarray) To UBound(myarray)
ActiveSheet.Columns(myarray(i)).EntireColumn.Delete
Next i
end sub
