Snasui Excel Forum

Code: Select all

Dim rall as Range, r As Range, i As Integer
Dim arr(99, 4) As Variant
With Sheets("Input")
    Set rall = .Range("c3", .Range("c" & .Rows.Count).End(xlUp))
    i = 2
    For Each r In rall
        If r.MergeCells Then
            i = i + 1
        End If
        If r.Value + r.Offset(0, 1).Value > 0 Then
            If r.Offset(0, -1).Value <> "" Then
                arr(i, 0) = r.Offset(0, -1).Value
            Else
                arr(i, 0) = r.Offset(0, -2).Value
            End If
            If r.Value > 0 Then
                arr(i, 3) = r.Value
            End If
            If r.Offset(0, 1).Value > 0 Then
                arr(i, 4) = r.Offset(0, 1).Value
            End If
            i = i + 1
        End If
    Next r
End With
With Worksheets("Forms")
    If i > 0 Then
        .Range("a:e").Clear
        .Range("a1:e1").Value = Array("Detail Estimate", "", "", _
            "Package", "Additional")
        .Range("a2").Resize(i, 5).Value = arr
    End If
End With

Code: Select all

'Other code
Dim arr(99, 4) As Variant, lr As Long
With Sheets("Input")
    lr = Application.Max(.Range("c" & .Rows.Count).End(xlUp).Row, _
        .Range("d" & .Rows.Count).End(xlUp).Row)     
    Set rall = .Range("c3:c" & lr)
'Other