excel - 删除除包含 X 值的列之外的所有列

Question

我正在尝试删除除名称之外的所有列，当前下面的代码将删除名称。

Private Sub CommandButton2_Click()

Dim i As Integer
Dim A As Range

 For i = 100 To 1 Step -1
 Set A = Cells(1, i).Find(What:="Names", LookIn:=xlValues)
 
 If Not A Is Nothing Then A.EntireColumn.Delete
 Next i
End Sub

更新代码：

Private Sub CommandButton1_Click()

Dim i As Integer
Dim A As Range


 If Cells(1, i) <> "Names" Then
 
 If A Is Nothing Then A.EntireColumn.Delete
 End If
End Sub

标签： excelvba

Answer 1

我不敢相信没有更有效的方法，但这应该可行。

为了允许 Names 列在任何地方，首先我将它移动到第 1 列，然后再删除其他所有内容。100 是硬编码的，因此您可能希望更改它。

Private Sub CommandButton1_Click()

Dim i As Long 'better than integer
Dim A As Range

Set A = Rows(1).Find(What:="Names", LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)

If Not A Is Nothing Then 'Names found
    Columns(A.Column).Cut
    Range("A1").Insert
    Range("B1").Resize(, 99).EntireColumn.Delete
Else
    Range("A1").Resize(, 100).EntireColumn.Delete 'delete all columns as Names not found
End If

End Sub