Insert new rows based on criteria in VBA

[Gutierrez]

Hello all ... need your help once again.

here is my problem ...

I have a worksheet with data in it .. in column"z" i have a number value ... what i would like to do .. is

1) insert a row after a certain criteria is met ie.

if column z row 1 = 20
row 2 = 20
row 3 =20
row 4 =30

i would want to insert a row between row 3 and 4

2) i would like to total the numbers above the column that i just inserted

so basically trying to do a grouping with totals.

any help would be appreciated.

 

[Gutierrez]

Geoff ... above is the lastest and greatest code ... thanks again ... but what i want to do now is subtotal each group in the row i created ... can u provide some help .. maybe a code to loop thru and get a start and end ... then summ everything in between?

 

[xlbo]

If you are inserting 3 rows, which row does the subtotal go on ?

How about:

Create groups using vlookup
insert subtotals based on groups
insert extra 2 rows either above or below subtotal row....

would that process work for you ?? the subtotals would automatically associate themselves with the groups...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gutierrez]

well i did see the code for subtotals .. but it didnt look quite like i had wanted ... so ... what i have currently looks good ... just need the totals in the middle of the 3 rows created

 

[xlbo]

What I am saying is that if you use the SUBTOALS method, you do not have to figure out where to do all your summing from and to - it will be done automatically

you can then loop through the rows and insert a row before and after the subtotals to create the desired effect

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gutierrez]

ok .. but i have 7 columns to get a subtotal for ... also i dont want anything but the subtotal ... and the grand total .. for each column ... what would the code look like to do that???

 

[xlbo]

easiest way is to do it manually and record yourself doing it with Tools>Macro>Record New Macro

This will generate code for you which you should then be able to fit into your existing code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question