比如sheet1 sheet2 sheet5 都含有共同一个字“黄”的行(可以是黄一、黄二、只要这一行里面含有这个字就要删掉整行)怎么通过函数来删除所有工作表含有的带这个字的整行呢补充问题
实际运用可能不是sheet1 sheet2这样命名 是直接没有规律的中文命名(日本、美国)又应该怎么操作
一个Excel表里面的sheet1和sheet2....sheet100批量删除含有相同一个字或两个字的行
答案:4 悬赏:80 手机版
解决时间 2021-12-16 22:14
- 提问者网友:转身→时光静好
- 2021-12-15 23:38
最佳答案
- 五星知识达人网友:已觉春心动
- 2021-12-16 00:55
用函数的话,没办法删除所有的工作表,最多是一个一个删除,
用EXCEL自带的VBA会好点
以下是代码:
Sub fdasf()
Dim sh As Worksheet
Dim rg As Range
Dim r As Long, c As Long, k As Long
Dim i As Long, j As Long
Dim arr(1 To 100000)
str_delete = InputBox("请输入你要查找的关键字")
If str_delete = "" Then Exit Sub
k = 0
For Each sh In Sheets
r = sh.UsedRange.Rows.Count
c = sh.UsedRange.Columns.Count
For i = 1 To r
For j = 1 To c
If str_delete Like sh.UsedRange.Cells(i, j) Then
k = k + 1
arr(k) = i
Exit For
End If
Next
Next
'Stop
If k > 0 Then
For i = k To 1 Step -1
sh.Rows(arr(i)).Delete
Next
End If
k = 0
Next
End Sub
用EXCEL自带的VBA会好点
以下是代码:
Sub fdasf()
Dim sh As Worksheet
Dim rg As Range
Dim r As Long, c As Long, k As Long
Dim i As Long, j As Long
Dim arr(1 To 100000)
str_delete = InputBox("请输入你要查找的关键字")
If str_delete = "" Then Exit Sub
k = 0
For Each sh In Sheets
r = sh.UsedRange.Rows.Count
c = sh.UsedRange.Columns.Count
For i = 1 To r
For j = 1 To c
If str_delete Like sh.UsedRange.Cells(i, j) Then
k = k + 1
arr(k) = i
Exit For
End If
Next
Next
'Stop
If k > 0 Then
For i = k To 1 Step -1
sh.Rows(arr(i)).Delete
Next
End If
k = 0
Next
End Sub
全部回答
- 1楼网友:矢遇绝望
- 2021-12-16 04:21
你的想像太丰富了,这个用函数是做不了的。只有用VBA可以实现。而且要用好多代码,首先要查找某一个或几个字,然后才可以删除行。
- 2楼网友:烈酒醉人
- 2021-12-16 03:16
右击工作表标签,查看代码,把下面的代码贴过去,按一下f5
sub chiefzjh()
for i%=1 to worksheets.count
worksheets(i).name=sheets(i).range("p2").value
next i
end sub
- 3楼网友:萝莉收纳盒
- 2021-12-16 01:40
建议用VBA来完成。
我要举报
如以上问答信息为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯