I am sure this is easy to you experts but I have several thousands rows of
data, and I want a macro to insert two rows every time it sees a "1" in one
column. I am doing this manually at the moment and I am falling asleep. I
would also like to be able to put a sub-total in column c after the lines
have been inserted, work out what percentage each part is of the sub-total,
and highlight this by inseting a "1" in column E next to the largest
percentage. I have put a before and after below.

P.S. I can just about record a macro, so as simple as possible would be good.

Thanks in advance.

BEFORE
A B C D E F
1 1 294 A
2 2 406 total B
3 1 - F
4 2 30,728 total T
5 1 14,833 A
6 2 13,820 total X
7 1 17,763 H
8 2 16,754 A
9 3 12,564 total B


AFTER
A B C D E F
1 1 294 42% A
2 2 406 58% 1 total B
3 700
4
5 1 - 0% F
6 2 30,728 100% 1 total T
7 30,728
8
9 1 14,833 52% 1 A
10 2 13,820 48% total X
11 28,654
12
13 1 17,763 38% 1 H
14 2 16,754 36% A
15 3 12,564 27% total B
47,081

Sub maketotal()

RowCount = 1
FirstRow = 1
Do While Range("A" & RowCount) < ""
If Range("B" & (RowCount + 1)) = 1 Or _
Range("B" & (RowCount + 1)) = "" Then

'add two blank row
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Range("B" & (RowCount + 1)) = "Total"
Range("C" & (RowCount + 1)).Formula = _
"=Sum(C" & FirstRow & ":C" & RowCount & ")"

RowCount = RowCount + 3
FirstRow = RowCount
Else
RowCount = RowCount + 1
End If
Loop

End Sub


"Nick C" wrote:

I am sure this is easy to you experts but I have several thousands rows of
data, and I want a macro to insert two rows every time it sees a "1" in one
column. I am doing this manually at the moment and I am falling asleep. I
would also like to be able to put a sub-total in column c after the lines
have been inserted, work out what percentage each part is of the sub-total,
and highlight this by inseting a "1" in column E next to the largest
percentage. I have put a before and after below.

P.S. I can just about record a macro, so as simple as possible would be good.

Thanks in advance.

BEFORE
A B C D E F
1 1 294 A
2 2 406 total B
3 1 - F
4 2 30,728 total T
5 1 14,833 A
6 2 13,820 total X
7 1 17,763 H
8 2 16,754 A
9 3 12,564 total B


AFTER
A B C D E F
1 1 294 42% A
2 2 406 58% 1 total B
3 700
4
5 1 - 0% F
6 2 30,728 100% 1 total T
7 30,728
8
9 1 14,833 52% 1 A
10 2 13,820 48% total X
11 28,654
12
13 1 17,763 38% 1 H
14 2 16,754 36% A
15 3 12,564 27% total B
47,081

Many thanks Joel. Could I impose on you further by asking you what to do with
it now?

Regards,

Nick

"Joel" wrote:

Sub maketotal()

RowCount = 1
FirstRow = 1
Do While Range("A" & RowCount) < ""
If Range("B" & (RowCount + 1)) = 1 Or _
Range("B" & (RowCount + 1)) = "" Then

'add two blank row
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Range("B" & (RowCount + 1)) = "Total"
Range("C" & (RowCount + 1)).Formula = _
"=Sum(C" & FirstRow & ":C" & RowCount & ")"

RowCount = RowCount + 3
FirstRow = RowCount
Else
RowCount = RowCount + 1
End If
Loop

End Sub


"Nick C" wrote:

I am sure this is easy to you experts but I have several thousands rows of
data, and I want a macro to insert two rows every time it sees a "1" in one
column. I am doing this manually at the moment and I am falling asleep. I
would also like to be able to put a sub-total in column c after the lines
have been inserted, work out what percentage each part is of the sub-total,
and highlight this by inseting a "1" in column E next to the largest
percentage. I have put a before and after below.

P.S. I can just about record a macro, so as simple as possible would be good.

Thanks in advance.

BEFORE
A B C D E F
1 1 294 A
2 2 406 total B
3 1 - F
4 2 30,728 total T
5 1 14,833 A
6 2 13,820 total X
7 1 17,763 H
8 2 16,754 A
9 3 12,564 total B


AFTER
A B C D E F
1 1 294 42% A
2 2 406 58% 1 total B
3 700
4
5 1 - 0% F
6 2 30,728 100% 1 total T
7 30,728
8
9 1 14,833 52% 1 A
10 2 13,820 48% total X
11 28,654
12
13 1 17,763 38% 1 H
14 2 16,754 36% A
15 3 12,564 27% total B
47,081

If you're new to macros, you may want to read David McRitchie's intro
at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nick C" wrote in message
...
Many thanks Joel. Could I impose on you further by asking you what to do
with
it now?

Regards,

Nick

"Joel" wrote:

Sub maketotal()

RowCount = 1
FirstRow = 1
Do While Range("A" & RowCount) < ""
If Range("B" & (RowCount + 1)) = 1 Or _
Range("B" & (RowCount + 1)) = "" Then

'add two blank row
Rows(RowCount + 1).Insert
Rows(RowCount + 1).Insert
Range("B" & (RowCount + 1)) = "Total"
Range("C" & (RowCount + 1)).Formula = _
"=Sum(C" & FirstRow & ":C" & RowCount & ")"

RowCount = RowCount + 3
FirstRow = RowCount
Else
RowCount = RowCount + 1
End If
Loop

End Sub


"Nick C" wrote:

I am sure this is easy to you experts but I have several thousands rows
of
data, and I want a macro to insert two rows every time it sees a "1" in
one
column. I am doing this manually at the moment and I am falling asleep.
I
would also like to be able to put a sub-total in column c after the
lines
have been inserted, work out what percentage each part is of the
sub-total,
and highlight this by inseting a "1" in column E next to the largest
percentage. I have put a before and after below.

P.S. I can just about record a macro, so as simple as possible would be
good.

Thanks in advance.

BEFORE
A B C D E F
1 1 294 A
2 2 406 total B
3 1 - F
4 2 30,728 total T
5 1 14,833 A
6 2 13,820 total X
7 1 17,763 H
8 2 16,754 A
9 3 12,564 total B


AFTER
A B C D E F
1 1 294 42% A
2 2 406 58% 1 total B
3 700
4
5 1 - 0% F
6 2 30,728 100% 1 total T
7 30,728
8
9 1 14,833 52% 1 A
10 2 13,820 48% total X
11 28,654
12
13 1 17,763 38% 1 H
14 2 16,754 36% A
15 3 12,564 27% total B
47,081