How to construct for best speed?

[Stuart]

I have data throughout cols A:G
User cycles down col H and enters an identifier in
the cell in that column to indicate the adjacent data
to be copied. They do the same in col I and col J,
where relevent.
All the values in cols H:J will be unique, and likely
to be 'A', 'B', 'C' or 'A1', 'A2', etc.

The 3 identifier cols represent 3 different types of
Contractor that the user will want to send data,
ie Subcontractor, Supplier and Labour-only.

I have another workbook open, containing an empty
sheet for each of the identifiers, and my thought was to
copy 'tagged' data into the appropriate sheet.

The 'tagged' records need to appear in the sequence
found in the original worksheet. So this suggests that
I've got to sample down cols H , I and J

Should I sample down each of these cols in turn, or
sample down col H and test, then using Offset test the
adjacent cells in I and J, before continuing down H?

Whichever, it seems there's going to be a lot of
If Not IsEmpty(Cell) happening.

Generally speaking, the entire record occupies a
single row. So where an identifier is found, just copy
that row. However, sometimes that is not the case
and the record might take this form:

A Data.............. 4 No 5.00 20.00

General information
relating to next record

B Different Data 6 No 7.00 42.00 K"

So K* is the tag indicating the data is to be copied
but this time the "General information relating to next
record" data also has to be copied with the
'B' record.

I'm reasonably happy that Offset. End(xlUp) & (Down)
will get this record. I mention this type of data in case it
precludes another solution.

The records are not contiguous. Blank/empty rows
exist.

Any general thoughts before I get stuck in to this, would
be much appreciated, thanks.

Using Xl2000 (ditto Users).

Regards.

 

[Stuart]

Is that: test each cell in one column, then move on to
the next column and ditto,
or
test one column, and then test across the columns
in that row, please?

Never been good at cards, and would hate to
louse up the deal.

Regards and thanks.

 

[Tom Ogilvy]

If I understood him correctly he already said some single entries were two
rows and there are blank rows between entries. So I am not sure sorting
would be advised if this is a form for interacting with the user.

But I could be wrong.

 

[Tom Ogilvy]

Assume your range is H2:J50

For each cell in Range("H2:J50").SpecialCells(xlConstants)
if cells(cell.row,1)="B" then
' copy two rows starting with row above
cell.offset(-1,0).entireRow.Resize(2,7).Copy _
Destination:=worksheets(cell.Value). _
Cells(row.count,1).End(xlup)(2)
else
' copy one row
cell.EntireRow.Resize(1,7).Copy _
Destination:=worksheets(cell.Value). _
Cells(row.count,1).End(xlup)(2)
End if
Next

If that doesn't give you the order you want, then you will have to loop
through all the cells in the order you want.

Name your sheets with the code in H2:J50, then after you have distributed
the data, rename the sheets with functional names - then you don't have to
translate on each copy.

 

[Stuart]

Making progress with your help.

Regards and thanks.