vba 删除除具有特定标题的列之外的所有列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16597841/
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
Deleting all columns except columns with certain headings
提问by Malkav
I am trying to format exported data and need to delete several columns. I want to keep columns with certain headings. For convenience if I have 15 columns and want to keep columns with the following headings;
我正在尝试格式化导出的数据并需要删除几列。我想保留带有某些标题的列。为方便起见，如果我有 15 列并希望保留带有以下标题的列；
State City Name Client Product
州城市名称客户产品
My concern is that the data I am exporting can change and columns could be added in the future. I want to be able to filter out unwanted columns and just keep the aforementioned columns.
我担心的是，我正在导出的数据可能会更改，并且将来可能会添加列。我希望能够过滤掉不需要的列并保留上述列。
Edit: I am using Excel and I have never written a line of code before in my life. Ever.
编辑：我正在使用 Excel，我一生中从未写过一行代码。曾经。
回答by David Zemens
Try this one.
试试这个。
Iterate over the columns in reverse order, check the headers in a Select Case, and delete as needed.
以相反的顺序遍历列，检查 Select Case 中的标题，并根据需要删除。
Sub deleteIrrelevantColumns()
Dim currentColumn As Integer
Dim columnHeading As String
ActiveSheet.Columns("L").Delete
For currentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1
columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value
'CHECK WHETHER TO KEEP THE COLUMN
Select Case columnHeading
Case "State", "City", "Name", "Client", "Product"
'Do nothing
Case Else
'Delete if the cell doesn't contain "Homer"
If Instr(1, _
ActiveSheet.UsedRange.Cells(1, currentColumn).Value, _
"Homer",vbBinaryCompare) = 0 Then
ActiveSheet.Columns(currentColumn).Delete
End If
End Select
Next
End Sub
回答by Tom Ridd
I'm guessing we're talking Excel here. If so iterating through your columns with a while loop and deciding to keep or not to keep should do the trick.
我猜我们在这里谈论的是 Excel。如果是这样，使用 while 循环遍历您的列并决定保留或不保留应该可以解决问题。
Sub deleteIrrelevantColumns()
Dim keepColumn As Boolean
Dim currentColumn As Integer
Dim columnHeading As String
currentColumn = 1
While currentColumn <= ActiveSheet.UsedRange.Columns.Count
columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value
'CHECK WHETHER TO KEEP THE COLUMN
keepColumn = False
If columnHeading = "State" Then keepColumn = True
If columnHeading = "City" Then keepColumn = True
If columnHeading = "Name" Then keepColumn = True
If columnHeading = "Client" Then keepColumn = True
If columnHeading = "Product" Then keepColumn = True
If keepColumn Then
'IF YES THEN SKIP TO THE NEXT COLUMN,
currentColumn = currentColumn + 1
Else
'IF NO DELETE THE COLUMN
ActiveSheet.Columns(currentColumn).Delete
End If
'LASTLY AN ESCAPE IN CASE THE SHEET HAS NO COLUMNS LEFT
If (ActiveSheet.UsedRange.Address = "$A") And (ActiveSheet.Range("$A").Text = "") Then Exit Sub
Wend
End Sub
回答by enick_87
I had a similar problem and this is the code that worked for me. I think it is much simpler.
我有一个类似的问题，这是对我有用的代码。我认为这要简单得多。
Range("A1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "Forecast Status" _
Or ActiveCell.Value = "Amount " _
Or ActiveCell.Value = "Service Booking Value " _
Or ActiveCell.Value = "Transaction Number" _
Or ActiveCell.Value = "Last Modified by" _
Or ActiveCell.Value = "Last Modified Date" _
Or ActiveCell.Value = "T# Comparison" _
Or ActiveCell.Value = "Amount Comparison" _
Or ActiveCell.Value = "Forecast Status Comparison" _
Or ActiveCell.Value = "First Ship Date Comparison" Then
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.EntireColumn.Select
Selection.Delete Shift:=xlToLeft
Selection.End(xlUp).Select
End If
Loop
