脚本运行良好。除非我不按"Alt + Tab"2-3次，否则至少还需要30分钟才能完成。但是如果我使用"Alt + Tab"切换，它会在不到 2 分钟的时间内完成。
我的问题是：
- 是由于循环使用不当吗？
- 是由于我使用的函数调用次数吗？
- 还是代码本身效率低下？
如果我错过了任何相关信息，请告诉我。
Private Sub CommandButton1_Click()
Call Interfac
Call DeleteRowBasedOnCriteria
Call DeleteRowBasedOnCriteria2
GenerateReport Worksheets("Report_Template"), Worksheets("Jira"), Worksheets("Script")
Call Deleterows
Call DefaultData
MsgBox "Report Generation Finished!"
End Sub
Public costcenterswitch As Long
Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Sheets("Jira").Select
For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 2)
If .Value = "CVEI-VR " _
Or .Value = "All Issues" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
Sub DeleteRowBasedOnCriteria2()
Dim RowToTest As Long
Sheets("Jira").Select
For RowToTest = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
With Cells(RowToTest, 1)
If .Value = "All Assignees" _
Then _
Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub
Sub DefaultData()
For Row = 2 To ActiveSheet.UsedRange.Rows.Count
Cells(Row, 1).Formula = 1
Cells(Row, 3).Formula = "SVDO"
Cells(Row, 4).Formula = costcenterswitch
Cells(Row, 5).Formula = "PS_99999"
Cells(Row, 9).Formula = 999
Cells(Row, 10).Formula = "EWH"
Cells(Row, 12).Formula = "H"
Cells(Row, 13).Formula = 0
Cells(Row, 14).Formula = 0
Cells(Row, 2).Formula = Row - 1
Next Row
End Sub
Sub Deleterows()
On Error Resume Next
Columns("K").SpecialCells(xlBlanks).EntireRow.Delete
End Sub
Sub Interfac()
Sheets("Script").Select
If IsEmpty(Range("O3").Value) = False Then
costcenterswitch = Range("O3").Value
Else
costcenterswitch = 900214
End If
End Sub
Sub GenerateReport(ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet)
Dim report As Workbook
Set report = Workbooks.Add
Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
Dim Row As Long, col As Integer, row3 As Integer, col3 As Integer, runsthirtyonetimes As Integer
With ws1.UsedRange
ws1row = .Rows.Count
ws1col = .Columns.Count
End With
With ws2.UsedRange
ws2row = .Rows.Count
ws2col = .Columns.Count
End With
maxrow = ws1row
maxcol = ws1col
If maxrow < ws2row Then maxrow = ws2row
If maxcol < ws2col Then maxcol = ws2col
Row = 1
For col = 1 To ws1col
Cells(Row, col).Formula = ws1.Cells(Row, col).Formula
Cells(Row, col).Font.Bold = True
Next col
counter = 2
For Row = 2 To ws2row
For runsthirtyonetimes = 1 To 31
Cells(counter, 7).Formula = ws2.Cells(Row, 2).Formula
Cells(counter, 8).NumberFormat = "yyyy-mm-dd"
Cells(counter, 8).Formula = ws2.Cells(Row, counter).Formula
Cells(counter, 11).Formula = ws2.Cells(Row, counter).Formula
If ws2.Cells(Row, 1).Formula = ws3.Cells(3, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(3, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(4, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(4, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(5, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(5, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(6, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(6, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(7, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(7, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(8, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(8, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(9, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(9, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(10, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(10, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(11, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(11, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(12, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(12, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(13, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(13, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(14, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(14, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(15, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(15, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(16, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(16, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(17, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(17, 17).Formula
ElseIf ws2.Cells(Row, 1).Formula = ws3.Cells(18, 16).Formula Then
Cells(counter, 6).Formula = ws3.Cells(18, 17).Formula
Else
Cells(counter, 6).Formula = "BAD ID"
Cells(counter, col).Interior.Color = RGB(200, 0, 0)
Cells(counter, col).Font.Bold = True
End If
counter = counter + 1
Next runsthirtyonetimes
Next Row
counter = 2
For Row = 2 To ws2row
For runsthirtyonetimes = 4 To 34
Cells(counter, 8).Formula = ws2.Cells(1, runsthirtyonetimes).Formula
Cells(counter, 11).Formula = ws2.Cells(Row, runsthirtyonetimes).Formula
counter = counter + 1
Next runsthirtyonetimes
Next Row
Columns("A:Z").ColumnWidth = 20
Columns("G:G").ColumnWidth = 60
Rows("1:100").RowHeight = 15
End Sub
如果您跨多个工作表工作，则阐明工作表的单元格或区域将减少错误并提高速度。下面的代码说明了您缺少的内容。
Sub DeleteRowBasedOnCriteria()
Dim RowToTest As Long
Dim Ws As Worksheet
Set Ws = Sheets("Jira")
With Ws
For RowToTest = .Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
With .Cells(RowToTest, 2) '<~~ ws.Cells(RowToTest, 2)
If .Value = "CVEI-VR " _
Or .Value = "All Issues" _
Then _
Ws.Rows(RowToTest).EntireRow.Delete
End With
Next RowToTest
End Sub