|
目的是从第一列中把包含工作表名称的内容筛选出来并删除!代码如下,附件如下、、、、错误可能比较多,劳烦各位指导! Private Sub CommandButton1_Click() Dim arr, brr()
Dim n, i, sh As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
arr = Sheet1.[a1].CurrentRegion
For Each sh In Worksheets
For i = 2 To UBound(arr)
If arr(i, 1) = "*" & sh.Name & "*" Then
n = n + 1
ReDim Preserve brr(1 To 4, 1 To n)
brr(1, n) = arr(i, 1)
brr(2, n) = arr(i, 2)
brr(3, n) = arr(i, 3)
brr(4, n) = arr(i, 4)
Sheet1.Rows(i).Delete
End If
Next i
Next
sh.Range("A2").Resize(UBound(brr, 2), 4) = Application.Transpose(brr)
sh.[a1:d4] = Array(Array("关键词", "消费", "点击量", "展现量"))
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
- Sub test()
- Dim arr, brr(), crr(), i%, j%, k%, str$, x%, sht As Worksheet
- arr = ActiveSheet.Range("a1").CurrentRegion.Value
- ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
- ReDim crr(1 To UBound(arr), 1 To UBound(arr, 2))
- For Each sht In Worksheets
- If sht.Name <> ActiveSheet.Name Then
- str = sht.Name
- crr(1, 1) = "关键词": crr(1, 2) = "消费"
- crr(1, 3) = "点击量": crr(1, 4) = "展现量"
- k = 1
- For i = 2 To UBound(arr)
- If arr(i, 1) Like "*" & str & "*" Then
- k = k + 1
- For j = 1 To 4
- crr(k, j) = arr(i, j)
- Next
- arr(i, 1) = ""
- End If
- Next
- If k > 1 Then sht.Range("a1").Resize(k, 4) = crr
- End If
- Next
- k = 0
- For i = 1 To UBound(arr)
- If arr(i, 1) <> "" Then
- k = k + 1
- For j = 1 To UBound(arr, 2)
- brr(k, j) = arr(i, j)
- Next
- End If
- Next
- With ActiveSheet.Range("a1").CurrentRegion
- .ClearContents
- .Resize(k, UBound(arr, 2)) = brr
- End With
- End Sub
复制代码
|
|