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

wpryan

Well-known Member
Joined
May 26, 2009
Messages
534
Office Version
  1. 365
Platform
  1. Windows
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...
VBA Code:
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top