VBA代码优化需求：排除零值提取52周数据

Sub GetInWeekData()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' 重命名工作表变量避免与内置对象冲突
    Set sourceSheet = ThisWorkbook.Sheets("Tier 1")
    Set outputSheet = ThisWorkbook.Sheets("Tableua")
    
    Dim check As Boolean, rn As Integer, Status As String, Region As String
    Dim outputrow As Long, kmsIssued As Double, wkNo As Integer, CP As String
    
    ' 提取控制表参数并声明类型
    wkNo = Worksheets("Control").Range("E3").Value
    CP = Worksheets("Control").Range("E1").Value
    
    check = False: rn = 8: Region = "": outputrow = 2
    
    ' 动态找到"Control"所在行，适配不固定行数
    Do
        check = (sourceSheet.Cells(rn, 2).Value = "Control")
        rn = rn + 1
    Loop Until check = True
    
    ' 从Control行上方开始倒序遍历数据行
    For X = rn - 2 To 8 Step -1
        ' 遍历第9到60列的周数据
        For Col = 9 To 60 Step 1
            Region = sourceSheet.Cells(X, 2).Value
            Status = sourceSheet.Cells(X, 4).Value
            
            ' 新增条件：仅当当前列数值大于0且第三列不为空时执行逆透视
            If sourceSheet.Cells(X, 3).Value <> "" And sourceSheet.Cells(X, Col).Value > 0 Then
                ' 写入区域
                outputSheet.Cells(outputrow, 1).Value = Region
                ' 写入DV
                outputSheet.Cells(outputrow, 2).Value = sourceSheet.Cells(X, 3).Value
                
                ' 根据Status设置类型标识
                Select Case Status
                    Case "Current", "Growth", "Attraction"
                        outputSheet.Cells(outputrow, 3).Value = IIf(sourceSheet.Cells(8, Col).Value <= wkNo, "A", "F")
                    Case "Target"
                        outputSheet.Cells(outputrow, 3).Value = "T"
                End Select
                
                ' 写入其他固定字段
                outputSheet.Cells(outputrow, 4).Value = CP
                outputSheet.Cells(outputrow, 5).Value = sourceSheet.Cells(8, Col).Value
                outputSheet.Cells(outputrow, 6).Value = "In Week Delivery"
                outputSheet.Cells(outputrow, 7).Value = sourceSheet.Cells(X, Col).Value
                outputSheet.Cells(outputrow, 8).Value = sourceSheet.Cells(X, 5).Value
                outputSheet.Cells(outputrow, 9).Value = sourceSheet.Cells(X, 7).Value
                
                outputrow = outputrow + 1
            End If
        Next Col
    Next X
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

• 零值过滤：在原有判断第三列不为空的条件中，新增And sourceSheet.Cells(X, Col).Value > 0，确保仅处理数值大于0的周数据。
• 代码简化：将Current/Growth/Attraction三个重复分支合并，用IIf函数压缩逻辑，减少冗余代码。
• 变量规范：为wkNo和CP补充类型声明，重命名Sheet为sourceSheet避免与Excel内置对象冲突，提升代码可读性。
• 行数适配：保留原有的Do...Loop动态查找"Control"行的逻辑，遍历范围会自动跟随数据行数变化调整。

内容的提问来源于stack exchange，提问作者user28091719