excel - 删除所有工作表中的所有列,命名的列除外
问题描述
我想删除 Excel 工作簿的所有工作表中的所有列,但名为:
Date
Name
Amount Owing
Balance
以下代码在活动工作表中工作:
Sub DeleteSelectedColumns()
Dim currentColumn As Integer
Dim columnHeading As String
For currentColumn = ActiveSheet.UsedRange.Columns.Count To 1 Step -1
columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value
'Check whether to preserve the column
Select Case columnHeading
'Insert name of columns to preserve
Case "Date", "Name", "Amount Owing", "Balance"
'Do nothing
Case Else
'Delete the column
ActiveSheet.Columns(currentColumn).Delete
End Select
Next
End Sub
如何修改此代码以应用于所有工作表?
解决方案
你正在寻找这样的东西:
Sub DeleteSelectedColumns()
Dim ws As Worksheet
Dim rDel As Range
Dim HeaderCell As Range
Dim sKeepHeaders As String
Dim sDelimiter as String
sDelmiter = ":"
sKeepHeaders = Join(Array("Date", "Name", "Amount Owing", "Balance"), sDelimiter)
For Each ws In ActiveWorkbook.Sheets
Set rDel = Nothing
For Each HeaderCell In ws.Range("A1", ws.Cells(1, ws.Columns.Count).End(xlToLeft)).Cells
If InStr(1, sDelimiter & sKeepHeaders & sDelimiter, sDelimiter & HeaderCell.Value & sDelimiter, vbTextCompare) = 0 Then
If Not rDel Is Nothing Then Set rDel = Union(rDel, HeaderCell) Else Set rDel = HeaderCell
End If
Next HeaderCell
If Not rDel Is Nothing Then rDel.EntireColumn.Delete
Next ws
End Sub
推荐阅读
- node.js - 如何使用 Nodejs 从 GPS 跟踪器 st-901 获取数据?
- python - Django nginx gunicorn 它们如何连接在一起?
- html - 如何在引导程序中将徽标图像、电话、位置放在导航栏上方?
- python - 使用python自动检查链接是否处于活动状态
- settings - 如何在 DNN 中使“在菜单中显示”默认设置关闭
- spring-boot - Failed to start bean 'inputBindingLifecycle' - A consumer group is required for a partitioned subscription
- amazon-web-services - Adding null partition value to the glue table
- python - Multiplication on the command line using argparse
- python - Python 导入 ModuleNotFoundError
- numpy - How Can I Find Peak Values of Defined Areas from Spectrogram Data using numpy?