My VB script runs as long as I don't switch windows by clicking “Alt + Tab”. Why?

Multi tool use
Multi tool use












0















The script works perfectly.
Except if I don't press "Alt + Tab" 2-3 times it will take at least another 30 minutes to finish. But If I switch using "Alt + Tab" it finishes in less than 2 minutes.



My question is :




  1. Is it due to improper use of loops ?

  2. Is it Due to number of function calls I have used ?

  3. Or the code itself is inefficient ?


Please let me know if I have missed any relevant info.



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









share|improve this question




















  • 1





    Qualify all your unqualified range, cells, columns calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook the range/cells/columns belong to.

    – chillin
    Dec 31 '18 at 12:25













  • Can you point out one of the unqualified range.cells.columns @chillin ?

    – Abhishek Singh
    Dec 31 '18 at 12:30











  • Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1

    – QHarr
    Dec 31 '18 at 12:39













  • Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.

    – QHarr
    Dec 31 '18 at 12:43











  • One example of an unqualified reference would be in your DeleteRowBasedOnCriteria procedure, For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row.... Even though you Select the intended parent worksheet on the line before, the call to Cells(Rows... is in and of itself unqualified. Also I think your DeleteRowBasedOnCriteria and DeleteRowBasedOnCriteria2 can be written using Range.AutoFilter. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to put Thisworkbook before all of your worksheets, but you said this is a VBScript?

    – chillin
    Dec 31 '18 at 12:53
















0















The script works perfectly.
Except if I don't press "Alt + Tab" 2-3 times it will take at least another 30 minutes to finish. But If I switch using "Alt + Tab" it finishes in less than 2 minutes.



My question is :




  1. Is it due to improper use of loops ?

  2. Is it Due to number of function calls I have used ?

  3. Or the code itself is inefficient ?


Please let me know if I have missed any relevant info.



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









share|improve this question




















  • 1





    Qualify all your unqualified range, cells, columns calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook the range/cells/columns belong to.

    – chillin
    Dec 31 '18 at 12:25













  • Can you point out one of the unqualified range.cells.columns @chillin ?

    – Abhishek Singh
    Dec 31 '18 at 12:30











  • Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1

    – QHarr
    Dec 31 '18 at 12:39













  • Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.

    – QHarr
    Dec 31 '18 at 12:43











  • One example of an unqualified reference would be in your DeleteRowBasedOnCriteria procedure, For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row.... Even though you Select the intended parent worksheet on the line before, the call to Cells(Rows... is in and of itself unqualified. Also I think your DeleteRowBasedOnCriteria and DeleteRowBasedOnCriteria2 can be written using Range.AutoFilter. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to put Thisworkbook before all of your worksheets, but you said this is a VBScript?

    – chillin
    Dec 31 '18 at 12:53














0












0








0








The script works perfectly.
Except if I don't press "Alt + Tab" 2-3 times it will take at least another 30 minutes to finish. But If I switch using "Alt + Tab" it finishes in less than 2 minutes.



My question is :




  1. Is it due to improper use of loops ?

  2. Is it Due to number of function calls I have used ?

  3. Or the code itself is inefficient ?


Please let me know if I have missed any relevant info.



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









share|improve this question
















The script works perfectly.
Except if I don't press "Alt + Tab" 2-3 times it will take at least another 30 minutes to finish. But If I switch using "Alt + Tab" it finishes in less than 2 minutes.



My question is :




  1. Is it due to improper use of loops ?

  2. Is it Due to number of function calls I have used ?

  3. Or the code itself is inefficient ?


Please let me know if I have missed any relevant info.



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






excel vba excel-vba






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 7 at 8:54









Pᴇʜ

22.2k42750




22.2k42750










asked Dec 31 '18 at 12:14









Abhishek SinghAbhishek Singh

184




184








  • 1





    Qualify all your unqualified range, cells, columns calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook the range/cells/columns belong to.

    – chillin
    Dec 31 '18 at 12:25













  • Can you point out one of the unqualified range.cells.columns @chillin ?

    – Abhishek Singh
    Dec 31 '18 at 12:30











  • Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1

    – QHarr
    Dec 31 '18 at 12:39













  • Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.

    – QHarr
    Dec 31 '18 at 12:43











  • One example of an unqualified reference would be in your DeleteRowBasedOnCriteria procedure, For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row.... Even though you Select the intended parent worksheet on the line before, the call to Cells(Rows... is in and of itself unqualified. Also I think your DeleteRowBasedOnCriteria and DeleteRowBasedOnCriteria2 can be written using Range.AutoFilter. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to put Thisworkbook before all of your worksheets, but you said this is a VBScript?

    – chillin
    Dec 31 '18 at 12:53














  • 1





    Qualify all your unqualified range, cells, columns calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook the range/cells/columns belong to.

    – chillin
    Dec 31 '18 at 12:25













  • Can you point out one of the unqualified range.cells.columns @chillin ?

    – Abhishek Singh
    Dec 31 '18 at 12:30











  • Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1

    – QHarr
    Dec 31 '18 at 12:39













  • Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.

    – QHarr
    Dec 31 '18 at 12:43











  • One example of an unqualified reference would be in your DeleteRowBasedOnCriteria procedure, For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row.... Even though you Select the intended parent worksheet on the line before, the call to Cells(Rows... is in and of itself unqualified. Also I think your DeleteRowBasedOnCriteria and DeleteRowBasedOnCriteria2 can be written using Range.AutoFilter. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to put Thisworkbook before all of your worksheets, but you said this is a VBScript?

    – chillin
    Dec 31 '18 at 12:53








1




1





Qualify all your unqualified range, cells, columns calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook the range/cells/columns belong to.

– chillin
Dec 31 '18 at 12:25







Qualify all your unqualified range, cells, columns calls, otherwise they implicitly refer to whatever sheet/workbook happens to be active whilst the code is running. This means you need to explicitly need to specify in your code what sheet/workbook the range/cells/columns belong to.

– chillin
Dec 31 '18 at 12:25















Can you point out one of the unqualified range.cells.columns @chillin ?

– Abhishek Singh
Dec 31 '18 at 12:30





Can you point out one of the unqualified range.cells.columns @chillin ?

– Abhishek Singh
Dec 31 '18 at 12:30













Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1

– QHarr
Dec 31 '18 at 12:39







Same goes for your Rows. You are, in places, using .Select to activate a sheet. It would be faster to use a With worksheets("sheetname") construct..... Example unqualified: Cells(Row, 1).Formula = 1

– QHarr
Dec 31 '18 at 12:39















Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.

– QHarr
Dec 31 '18 at 12:43





Was this code originally in several modules as you have a Public variable declaration hanging out lower in the code.

– QHarr
Dec 31 '18 at 12:43













One example of an unqualified reference would be in your DeleteRowBasedOnCriteria procedure, For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row.... Even though you Select the intended parent worksheet on the line before, the call to Cells(Rows... is in and of itself unqualified. Also I think your DeleteRowBasedOnCriteria and DeleteRowBasedOnCriteria2 can be written using Range.AutoFilter. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to put Thisworkbook before all of your worksheets, but you said this is a VBScript?

– chillin
Dec 31 '18 at 12:53





One example of an unqualified reference would be in your DeleteRowBasedOnCriteria procedure, For RowToTest = Cells(Rows.Count, 2).End(xlUp).Row.... Even though you Select the intended parent worksheet on the line before, the call to Cells(Rows... is in and of itself unqualified. Also I think your DeleteRowBasedOnCriteria and DeleteRowBasedOnCriteria2 can be written using Range.AutoFilter. It should be faster, less verbose and easier to maintain, as you won't be deleting one row at a time. I want to put Thisworkbook before all of your worksheets, but you said this is a VBScript?

– chillin
Dec 31 '18 at 12:53












1 Answer
1






active

oldest

votes


















0














If you work across multiple sheets, clarifying the cell or range of sheets will reduce errors and improve speed. The code below illustrates what you are missing.



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





share|improve this answer























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53987404%2fmy-vb-script-runs-as-long-as-i-dont-switch-windows-by-clicking-alt-tab-why%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    If you work across multiple sheets, clarifying the cell or range of sheets will reduce errors and improve speed. The code below illustrates what you are missing.



    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





    share|improve this answer




























      0














      If you work across multiple sheets, clarifying the cell or range of sheets will reduce errors and improve speed. The code below illustrates what you are missing.



      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





      share|improve this answer


























        0












        0








        0







        If you work across multiple sheets, clarifying the cell or range of sheets will reduce errors and improve speed. The code below illustrates what you are missing.



        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





        share|improve this answer













        If you work across multiple sheets, clarifying the cell or range of sheets will reduce errors and improve speed. The code below illustrates what you are missing.



        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






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 31 '18 at 13:00









        Dy.LeeDy.Lee

        3,6421510




        3,6421510
































            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53987404%2fmy-vb-script-runs-as-long-as-i-dont-switch-windows-by-clicking-alt-tab-why%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            jg0AeUcqo9OH m hiLqISvcSuUQbpbv DOOXMkJviZ5 5zFbyCoS kmG3RDIbLFIlgT
            u 1eTwO qe3y6VY,VE18vdH aCVbc6

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas