How to construct for best speed?
 

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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003

How to construct for best speed?
 

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" wrote in message
...
I think row at a time should be fast enough and would certainly be the
easiest - like dealing cards.

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003

How to construct for best speed?
 

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.

--
Regards,
Tom Ogilvy

Mark Bigelow wrote in message
...
If sorting wouldn't mess up your data, I would sort all the columns,
including H:J, by H, then by I, then by J. That way, you can start at
Row 1 through the last row of H that has data and put that Range into
the worksheet corresponding with H, then go from that last row + 1 to
the last row in I, etc...

Let me know if that doesn't work.

Mark

---
Mark Bigelow
mjbigelow at hotmail dot com
http://hm.imperialoiltx.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

How to construct for best speed?
 

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.

--
Regards,
Tom Ogilvy


Stuart wrote in message
...
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" wrote in message
...
I think row at a time should be fast enough and would certainly be the
easiest - like dealing cards.

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003

How to construct for best speed?
 

Making progress with your help.

Regards and thanks.

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


Stuart wrote in message
...
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" wrote in message
...
I think row at a time should be fast enough and would certainly be the
easiest - like dealing cards.

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003