Snasui Excel Forum

Code: Select all

Sub chk_stock()
    Dim r1 As Range, rt As Range, rs As Range, r As Range
    With Worksheets("sheet1")
        Set r1 = .Range("A7", .Range("A1536").End(xlUp))
    End With
    For Each r In r1
        If r.Offset(0, 15).Value <> 0 And IsNumeric(r.Offset(0, 15).Value) Then
            With Worksheets("Sheet1")
            Set rs = Union(r.Offset(0, 0), r.Offset(0, 1), r.Offset(0, 2), r.Offset(0, 3), r.Offset(0, 4), _
                     r.Offset(0, 15))
            End With
            Set rt = Worksheets("check_stock").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            rs.Copy rt
            Application.CutCopyMode = False
        End If
    Next r
    
         Worksheets("check_stock").Columns("A:AZ").EntireRow.AutoFit
        ' Worksheets("check_stock").Columns("A:AZ").RowHeight = 20
End Sub

Code: Select all

'Other code
        End With
        Set rt = Worksheets("check_stock").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        rt.Offset(0, 6).Resize(1, 2).Borders.LineStyle = xlContinuous
        rs.Copy rt
        Application.CutCopyMode = False
    End If
Next r
rt.Offset(1, 0).Value = "ลงชื่อ............"
'Other code