Running once through code does not give expected results, stepping through or running twice does

[wpryan]

Hi All, as the thread title suggests I have some code that is supposed to display the filtered results of a query to a database table on a worksheet, "Summary". The data filter is shown on another worksheet, "Data". Running through the code, the data in the Data worksheet is correctly updated by the code, but the Summary worksheet is not updated as expected if one runs through the code one time. If one runs through it again, the Summary worksheet is correctly updated. Additionally, if one just steps through the code it works as expected. The code is below. Can anyone help me understand why it is behaving this way, and help me to get it work correctly? Thanks in advance...

Private Sub Worksheet_Change(ByVal Target As Range)
    
If Target.Address = "$B$8" Then

Application.ScreenUpdating = False
     
    ActiveWorkbook.RefreshAll
    
'_____unhide all previously hidden rows
    unhide
    
'_____Populate General Data
    With Worksheets("Summary")
        .Range("B10").Value = Sheets("Data").Range("B2").Value
        .Range("B12").Value = Sheets("Data").Range("C2").Value
        .Range("E8").Value = Sheets("Data").Range("D2").Value
        .Range("E10").Value = Sheets("Data").Range("E2").Value
        .Range("E12").Value = Sheets("Data").Range("G2").Value
        .Range("F12").Value = Sheets("Data").Range("F2").Value
        .Range("C16").Value = Sheets("Data").Range("J2").Value
        .Range("E48").Value = Sheets("Data").Range("M2").Value

'____Populate Performed Tasks
        .Range("A19").Value = Sheets("Data").Range("H2").Value
        .Range("B19").Value = Sheets("Data").Range("I2").Value
        
        .Range("A20").Value = Sheets("Data").Range("H3").Value
        .Range("B20").Value = Sheets("Data").Range("I3").Value
        
        .Range("A21").Value = Sheets("Data").Range("H4").Value
        .Range("B21").Value = Sheets("Data").Range("I4").Value
        
        .Range("A22").Value = Sheets("Data").Range("H5").Value
        .Range("B22").Value = Sheets("Data").Range("I5").Value
        
        .Range("A23").Value = Sheets("Data").Range("H6").Value
        .Range("B23").Value = Sheets("Data").Range("I6").Value
        
        .Range("A24").Value = Sheets("Data").Range("H7").Value
        .Range("B24").Value = Sheets("Data").Range("I7").Value
        
        .Range("A25").Value = Sheets("Data").Range("H8").Value
        .Range("B25").Value = Sheets("Data").Range("I8").Value
        
        .Range("A26").Value = Sheets("Data").Range("H9").Value
        .Range("B26").Value = Sheets("Data").Range("I9").Value
        
        .Range("A27").Value = Sheets("Data").Range("H10").Value
        .Range("B27").Value = Sheets("Data").Range("I10").Value
        
        .Range("A28").Value = Sheets("Data").Range("H11").Value
        .Range("B28").Value = Sheets("Data").Range("I11").Value
         
    '____Populate Hours Tasks
        .Range("A33").Value = Sheets("Data").Range("H2").Value
        .Range("B33").Value = Sheets("Data").Range("K2").Value
        .Range("C33").Value = Sheets("Data").Range("L2").Value
        
        .Range("A34").Value = Sheets("Data").Range("H3").Value
        .Range("B34").Value = Sheets("Data").Range("K3").Value
        .Range("C34").Value = Sheets("Data").Range("L3").Value
        
        .Range("A35").Value = Sheets("Data").Range("H4").Value
        .Range("B35").Value = Sheets("Data").Range("K4").Value
        .Range("C35").Value = Sheets("Data").Range("L4").Value
        
        .Range("A36").Value = Sheets("Data").Range("H5").Value
        .Range("B36").Value = Sheets("Data").Range("K5").Value
        .Range("C36").Value = Sheets("Data").Range("L5").Value
        
        .Range("A37").Value = Sheets("Data").Range("H6").Value
        .Range("B37").Value = Sheets("Data").Range("K6").Value
        .Range("C37").Value = Sheets("Data").Range("L6").Value
        
        .Range("A38").Value = Sheets("Data").Range("H7").Value
        .Range("B38").Value = Sheets("Data").Range("K7").Value
        .Range("C38").Value = Sheets("Data").Range("L7").Value
        
        .Range("A39").Value = Sheets("Data").Range("H8").Value
        .Range("B39").Value = Sheets("Data").Range("K8").Value
        .Range("C39").Value = Sheets("Data").Range("L8").Value
        
        .Range("A40").Value = Sheets("Data").Range("H9").Value
        .Range("B40").Value = Sheets("Data").Range("K9").Value
        .Range("C40").Value = Sheets("Data").Range("L9").Value
        
        .Range("A41").Value = Sheets("Data").Range("H10").Value
        .Range("B41").Value = Sheets("Data").Range("K10").Value
        .Range("C41").Value = Sheets("Data").Range("L10").Value
        
        .Range("A42").Value = Sheets("Data").Range("H11").Value
        .Range("B42").Value = Sheets("Data").Range("K11").Value
        .Range("C42").Value = Sheets("Data").Range("L11").Value
        
    End With
    
    Dim rCell As Range, dCell As Range, rng1 As Range, rng2 As Range, rngData1 As Range, rngData2 As Range

    Set rng1 = Worksheets("Summary").Range("A19:A28").Cells
    Set rng2 = Worksheets("summary").Range("A33:A42").Cells
    Set rngData1 = Worksheets("Data").Range("I2:I11").Cells
    Set rngData2 = Worksheets("Data").Range("H2:H11").Cells

    For Each rCell In rng1
        If (rCell.Value = "0" Or rCell.Value = "00.01.1900" Or rCell.Value = "" Or rCell.Value = "00:00:00") Then
            If rCell.EntireRow.Hidden = True Then
                GoTo 1
            Else:
                rCell.EntireRow.Hidden = True
            End If
        End If
    Next rCell
1
    For Each rCell In rng1
        If rCell.EntireRow.Hidden = False Then
            rCell.EntireRow.AutoFit
        End If
    Next rCell

    For Each dCell In rng2
        If (dCell.Value = "0" Or dCell.Value = "00.01.1900" Or dCell.Value = "" Or dCell.Value = "00:00:00") Then
            If dCell.EntireRow.Hidden = True Then
                GoTo 2
            Else:
                dCell.EntireRow.Hidden = True
            End If
        End If
    Next dCell

2
    For Each dCell In rng2
        If dCell.EntireRow.Hidden = False Then
            dCell.EntireRow.AutoFit
        End If
    Next dCell
    
Application.ScreenUpdating = True

End If

End Sub

 

[Legacy 456129]

Happened to me, looping through sheets selecting all shapes and at the end delete all shapes. Stepping through it works flawless, just run not at all. The code is in one workbook, the deleting is in a second workbook to which data ranges and tables were copied. Gave up and went back to a for next selecting all and delete per sheet.