Macro to add rows at a change in data

[Mr. T.]

Sample data :

COLORADO 014C 2391942 3528600 141
172 724899 1067108 40
1797 546812 820255 34
1998 416032 626383 28
2216 806535 1176044 44
237 1872461 2710125 104
2477 168605 253895 10
2830 1016305 1527912 64
3262 396654 557467 20
3524 1261749 1913467 79
3525 1704009 2442987 99
3609 614421 914285 33
3641 1204536 1760984 73
3704 1914318 2761913 109
3748 913416 1333302 54
3934 1355085 2068155 85
3938 857785 1273634 57
COLORADO TOTAL 18165564 26736516 1074
36331128 53473032 2148

Other info:

1. I used the subtotal command to get the last line of data. Note that the
row above it is the subtotal already in the spreadsheet.

2. In column a, there is a number that reflects the state in column B

3. Columns C- F have data that gets subtotaled.


How do I create a macro that:

1. inserts a row above and below where each subtotal occurs. Keep in mind it
would be a change in Column A (would prefer B).

2. After inserting the rows , I then need to delete the rows that has the
State Total already in the spreadsheet


3. Or better yet: lookup value in column a, preplace blank in column b with
dat from lookup table. At change in column b, insert row. Needs to be done in
this order, as if this is done before lookup/replace, I will have a break
from "state" and state total"

Thanks..

 

[Glen Mettler]

I am not sure what you are trying to do with the row insert. If you want
totals for all the states, I would set up a sheet (call it State Sum) with
all the states listed in column A and the totals in columns B,C, & D.
Then set up 50 sheets and name them approprately with the data in A, B, C
Then, instead of having you total at the bottm, put your total in row 1 for
each column. That way, it doesn't matter if the rows are different for each
state. The totals are always in row 1.

Sheet State Sum
A B C D
State Tot1 Tot2 Tot3
AL
AK
AZ
AR
CA
CO =CO!A1 =CO!B1 =CO!C1
CT

Thus, whenever a total changes in any state, the values in the State Sum
sheet will automatically be updated.

Hope this helps

Glen