gpt4 book ai didi

excel - 如何按标题删除多列?

转载 作者:行者123 更新时间:2023-12-04 21:42:36 25 4
gpt4 key购买 nike

我有一个包含大约 80 列的工作表,我想删除其中的一些列,并且这些列会持续更新并且可能会四处移动,因此我想通过列标题删除它们。但是,当我运行这个宏时,它可以工作,但我实际上需要运行它至少 4-5 次才能完全删除所有正确的列。这里发生了什么?有没有正确和更优雅的方法来做到这一点?

Sub Modify_Table()
' Remove specific columns
Set MR = ActiveSheet.Range("A1:BQ1")

For Each cell In MR
If cell.Value = "Attack ID" Then
cell.EntireColumn.Delete
ElseIf cell.Value = "Last Change (UTC)" Then
cell.EntireColumn.Delete
ElseIf cell.Value = "Tools/Malware" Then
cell.EntireColumn.Delete
ElseIf cell.Value = "Labels" Then
cell.EntireColumn.Delete
ElseIf cell.Value = "Attacker Profile" Then
cell.EntireColumn.Delete
ElseIf cell.Value = "Leak Rate" Then
cell.EntireColumn.Delete
ElseIf cell.Value = "Footprint" Then
cell.EntireColumn.Delete
ElseIf cell.Value = "Target Node" Then
cell.EntireColumn.Delete
ElseIf cell.Value = "Test Time (UTC)" Then
cell.EntireColumn.Delete
...
Else
End If
Next

End Sub

最佳答案

删除项目时从最后一项迭代到第一项。否则,您最终会跳过项目。这是因为当您删除一个项目时,项目会发生偏移
在这种情况下,我将创建一个包含所有要删除的标题的范围,然后一次将它们全部删除。

Sub Modify_Table()
' Remove specific columns
Dim HeaderRange As Range

Set HeaderRange = ActiveSheet.Range("A1:BQ1")

Dim Target As Range
Dim Cell As Range
Dim Titles As Variant
Titles = Array("Attack ID", "Last Change (UTC)", "Tools/Malware", "Labels", "Attacker Profile", "Leak Rate", "Footprint", "Target Node", "Test Time (UTC)")

For Each Cell In HeaderRange
If Not IsError(Application.Match(Cell.Value, Titles, 0)) Then
If Target Is Nothing Then
Set Target = Cell
Else
Set Target = Union(Target, Cell)
End If
End If
Next

If Not Target Is Nothing Then Target.EntireColumn.Delete

End Sub

关于excel - 如何按标题删除多列?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/72190073/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com