word、wps中使用vba删除所有表格指定列
处理word表格搞得人头大,找时间百度一波写了个函数处理标记删除的问题,根据这一套逻辑还可以实现很多功能,供各位参考。
要点:
双循环遍历所有表表头
InStr函数匹配具体内容(word表格多半有特殊字符,相等匹配较难保证)
在内循环结束后进行删除,否则将出错
用法:
1、定义条件,如 InStr(value1, “学号”),多条件拼接(如 InStr(value1, “学号”)>0 or InStr(value1, “姓名”) >0)
2、新建宏名range,粘贴内容到vba编辑器
3、执行
效果:
代码:
'ver 2.0
Sub range()
'遍历文档所有表格,删除表头第一行含有特定字符串的列
Dim value1 As String
Dim target() As Integer
For i = 1 To ActiveDocument.Tables.Count
Dim targetLen As Integer
targetLen = 0
For j = 1 To ActiveDocument.Tables(i).Columns.Count
value1 = ActiveDocument.Tables(i).Columns(j).Cells(1).range.Text
'这里定义条件,可以定义多个条件
If InStr(value1, "测试") > 0 or InStr(value1, "学号") > 0 Then
targetLen = targetLen + 1
ReDim Preserve target(targetLen)
target(targetLen - 1) = j
Else
End If
Next j
If targetLen <> 0 Then
' MsgBox targetLen
' 修正删除操作带来的列号偏移
Dim shift As Integer
shift = 0
For k = 0 To (targetLen - 1)
' MsgBox target(k)
ActiveDocument.Tables(i).Columns(target(k) - shift).Delete
shift = shift + 1
Next k
End If
Next i
MsgBox ("执行完毕。")
End Sub
使用过程中遇到复合表格遍历报错问题,修改加入错误处理跳过
(注意,因需求变动这里已经改成了匹配第一列,如仍需匹配第一行,请按上一段代码循环条件设置)
'ver 3.0
Dim value1 As String
Dim target() As Integer
For i = 1 To ActiveDocument.Tables.Count
'MsgBox ("遍历表 " & i & "。")
Dim targetLen As Integer
targetLen = 0
For j = 1 To ActiveDocument.Tables(i).Rows.Count
On Error GoTo flag
value1 = ActiveDocument.Tables(i).Rows(j).Cells(1).range.Text
'这里定义条件
If InStr(value1, "test1") > 0 Or InStr(value1, "test2") > 0 Then
targetLen = targetLen + 1
ReDim Preserve target(targetLen)
target(targetLen - 1) = j
Else
End If
Next j
If targetLen <> 0 Then
' MsgBox targetLen
' 修正删除操作带来的列号偏移
Dim shift As Integer
shift = 0
For k = 0 To (targetLen - 1)
' MsgBox target(k)
ActiveDocument.Tables(i).Rows(target(k) - shift).Delete
shift = shift + 1
Next k
End If
flag:
Resume continue
continue:
Next i
MsgBox ("执行完毕。")
End Sub
如需删除而非跳过复合行,则删除代码需换成下列实现形式
With ActiveDocument
.Tables(1).Cell(2, 2).Delete ShiftCells:=wdDeleteCellsEntireRow
End With