insert rows

[Rager]

Hi there,
I'm not that familiar with macros and the information I'm finding is leading
me to believe this is what I need to be using. I currently have 8 different
sections in my worksheet and I am trying to figure out how to insert another
blank row in these sections (before the "total" row) when needed...of course
without interupting the rest of the worksheet. Does anyone have a simple
solution to this? Please be very specific in the actions I need to take to
achieve this.

 

[JulieD]

Hi Rager

i would recommend getting rid of the total rows as long as there is a piece
of information in your workbook to specify which group they belong to, then
when new rows are added all you have to do is to sort by the field that you
want to group the data on and then choose data / subtotals and Excel will
subtotal the data for you automatically

this structure will also allow you to use pivot tables, Data / Form to enter
data into the workbook (as long as you've got less than 30 columns of data),
and data / filter / autofilters ...........

here's an example on using data / subtotals:

.........A..................B......................C
1....Dept..............Person.............Amount
2....A...................Fred...............50.00
3....B...................Steve..............100.00
4....A...................Anne................20.00

is how the workbook currently appears, now i want subtotals on dept, so i
click on cell A2, choose data / sort - Dept (and while i'm there do a
secondary sort on Person)
then choose Data / Subtotal
at each change in Dept
SUM
Amount
click OK
and i end up with

.........A..................B......................C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00
5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

Hope this helps
Cheers
JulieD

 

[Rager]

Thanks for the recommendation Julie...unforturnately, I have to keep the
totals column and row where they are. I've been reading some other posts and
some people have asked similar questions as I have. Again, there is this
"macro" talk and I am not sure exactly how and exactly what actions I need to
do to set this up. Any other suggestions from anyone??? I need help!

 

[JulieD]

Hi Rager

a macro basically will work for you where you can "automate" the process
without intervention ... which means that the macro has to be able to
determine WHERE to put the new row ... is there something in your workbook
that the code can use to determine this ... and
also we'll need a bit more of an idea of the structure of your worksheet ...
could you (using the same layout as i did in my example) give us an idea of
what you're worksheet's layout is. Please do not, however, attach a
workbook to your reply

Cheers
JulieD

 

[Rager]

Julie,
My layout is very similar to what you had:
..........A..................B......................C
1....Dept..............Person.............Amount
2....A...................Anne................20.00
3....A...................Fred...............50.00
4...Total A...................................70.00

5....B...................Steve..............100.00
6...Total B.................................100.00
7....Grand Total.........................170.00

I need to be able to add the rows because at any given time, you could have
20 names vs. 5. Again, I have about 8 different sections, like the above
layout, and I have a border around the section and also a sum formula in the
total cell.

I guess what would be ideal is to have some sort of button to add another
row or something above the total row to add rows. ??? If a macro is what I
need...how and what steps do I need to achieve this?

I appreciate your help Julie!

 

[JulieD]

Hi Rager

the main problem with this sort of macro is say the formula in C4 is
=SUM(C2:C3)
if you add in three rows you'ld want the formula to automatically update to
=SUM(C2:C6)
however this won't happen ... the easiest option is the inserting of a row
(can have row height set to 1 or whatever above the total row) so that in
the example above the first formula would be in C5 and read =SUM(C2:C4), the
new rows would be inserted above row C4 so the formula would automatically
adjust.

Can you worksheet structure be changed to accommodate this?

Cheers
JulieD

 

[Robert]

Rager, search "Insert a blank row(2)" and
"Insert a blank row". If you have the word
"Total" (or any identifiable word for every total line")
the codes provided by RON is very good. I do not know
VBA but manged to put together macro and codes to
achieve what I wanted. I have the same situation as
you ie. totals in different ranges. Record the macro
first using Data>Subtotal as JulieD suggested and I
am sure Ron will assist in inserting the Column.

RobertR

 

[Rager]

Julie,
First, I really appreciate your help. I don't see a problem with what you
are saying...just tell me exactly what I need to do and I will give it a try!
Thank you again for all your replies!

 

[Rager]

Thanks Robert...I posted my situation to Ron.

 

[JulieD]

Hi Rager

if you'ld like to email me direct i can send you a sample workbook and then
once we've checked its doing what you want i can walk you through setting it
up in your workbook. My email is julied_ng At hcts dot net dot au.

Cheers
JulieD