Hi Barb,

Thanks for that. I came up with a solution to the non-zero values
problem last night, but I'm still stumped by the insert in the right
chronological order problem.

Looking through some of my other spreadsheets I found I'd already
cracked the nil values one for a macro I did a couple of years ago to
amend the selected data for a chart, so I wouldn't chart zero series.
With a bit of modification it worked here.

The following function takes a specified range and returns a range
specifying only the rows where the numerical fields (columns 7 to 9)
aren't all zero. This range can be copied and pasted to the target
cell, but I'm still stumped by trying to figure out how to code for
the target row.

Pasting at the end of the data sheet and then running a sort would
work but wouldn't be ideal as occasionally there are pieces of data
which need to be in a specific order other than an exact chronolonical
ordering. (They need to be in the same order as the statement the
data has been copied from, to make it easier to check against the
statement during audit.)

Travis

Function NonZeroCommissions(inputrange As Range) As Range

Dim DownCounter, AcrossCounter, NumberOfRows As Integer
Dim temprange, copyable, upperleft As Range


NumberOfRows = inputrange.Rows.Count


Set upperleft = inputrange.Resize(1, 1)

' Find the first series which isn't all zeros, and name its range
"copyable"

For DownCounter = 0 To NumberOfRows

For AcrossCounter = 7 To 9

If Not upperleft.Offset(DownCounter, AcrossCounter).Value = 0 Then

Set copyable = Range(upperleft.Offset(DownCounter, 0).Address &
":" & upperleft.Offset(DownCounter, 21).Address)

Exit For

End If

Next AcrossCounter

Next DownCounter

' Now build up the rest of the range by adding additional ranges which
also have non zeros

For DownCounter = 0 To NumberOfRows

For AcrossCounter = 7 To 9

If Not upperleft.Offset(DownCounter, AcrossCounter).Value = 0
Then

Set temprange = Range(upperleft.Offset(DownCounter, 0).Address
& ":" & upperleft.Offset(DownCounter, 21).Address)
Set copyable = Union(copyable, temprange)

AcrossCounter = 1

Exit For

End If

Next AcrossCounter


Next DownCounter

Set NonZeroCommissions = copyable

End Function