-
我有一个 Web 应用程序,它生成两个不同的 Excel 报表,具有不同的叶子数和不同的列数,请参见下文。
报告 1
工作表 1 有四列标题为 C11、C12、C13、C14
Sheet2 有三列标题为 C21、C22、C23报告 2
Sheet1有四列,标题为C11,C12,C13,C14(与报告1相同(
Sheet2 有三列,标题为 C21、C22、C23(与报告 1 中的列相同(
工作表 3 有三列,标题为 C31、C32、C33、C34、C35,.... -
我希望能够删除我应该输入的两个级别的报告中的一些列,如下所示:
第 1 级:搜索 Sheet1 并删除 C12,然后
搜索 Sheet2 并删除 C22,然后搜索工作表3,并删除C32第 2 级:搜索工作表 1 并删除 C11 和 C13,然后
搜索 Sheet2 并删除 C21 和 C22,然后搜索工作表3,并删除C33,C34,C35 -
我希望宏首先要求级别,然后搜索每个工作表并期待每一列并如上所述将其删除。
使用以下代码。运行宏"选择"以选择级别。此代码删除条件为 true 的整个列
Sub Choose()
l = InputBox(Prompt:="Enter the level you want", Title:="Level Selection")
If l = 1 Then
Call Level1
ElseIf l = 2 Then
Level2
ElseIf l = "" Then
Else
MsgBox "Incorrect entry.", vbInformation, "Incorrect"
End If
End Sub
Sub Level1()
Application.ScreenUpdating = False
On Error Resume Next
Blad1.Activate
Blad1.Cells.Find(What:="C12", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Column
Blad1.Columns(i).Delete
Blad2.Activate
Blad2.Cells.Find(What:="C22", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Column
Blad2.Columns(i).Delete
Blad3.Activate
Blad3.Cells.Find(What:="C32", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Column
Blad3.Columns(i).Delete
Blad1.Activate
Application.ScreenUpdating = True
End Sub
Sub Level2()
Application.ScreenUpdating = False
On Error Resume Next
Blad1.Activate
Blad1.Cells.Find(What:="C11", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Column
Blad1.Columns(i).Delete
Blad1.Cells.Find(What:="C13", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Column
Blad1.Columns(i).Delete
Blad2.Activate
Blad2.Cells.Find(What:="C21", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Column
Blad2.Columns(i).Delete
Blad2.Cells.Find(What:="C22", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Column
Blad2.Columns(i).Delete
Blad3.Activate
Blad3.Cells.Find(What:="C33", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Column
Blad3.Columns(i).Delete
Blad3.Cells.Find(What:="C34", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Column
Blad3.Columns(i).Delete
Blad3.Cells.Find(What:="C35", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
i = ActiveCell.Column
Blad3.Columns(i).Delete
Blad1.Activate
Application.ScreenUpdating = True
End Sub
请参阅包含宏的文件