Using "Find"

  • Thread starter Thread starter pk
  • Start date Start date
P

pk

Hello, I hope someone can help me with this, please.

I am using VBA in Excel XP with Windows 2000.

I apologize in advance for the length of this request.

I need to build some code that will do the following
using "Find". I wish to use "Find" because I am working
with large spreasheets (20,000 to 40,000 rows; 20 to 50
columns) and I think "Find" runs faster than anything else.

Data is imported using DAO into a sheet. In a column there
may be 30 to 60 or more dynamic transaction types, these
types are variable and not all appear everytime, so my
code needs to be dynamic. First transaction type always
appears in "E2" to start (below header row).

1. Use find to locate the LAST occurrence in the column of
the first transaction type. Among other things, it will
insert a couple blank rows beneath the last occurrence to
separate the data in the sheet by tran-type.

2. Code would then locate the LAST occurrence of the next
transaction type and repeat requirements of step 1 above.

3. Repeat these procedures until all transaction types
have been processed.

Your example code would be most appreciated. Thanks in
advance for your assistance on this.
 
I assume the transaction types are grouped. I would use the built in
Subtotal function under the data menu.

This will put subtotals below each transaction types if you select that as
the grouping column. You can then Delete those subtotals and remove the
outlining on the left. Then use specialcells(xlblanks) to reference these
subtotal rows and insert additional.

Turning on the macro recorder while doing it manually will give you the
basic code you need.
 
Back
Top