Inserting a row
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 |
Inserting a row
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 |
Inserting a row
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 |
All times are GMT +1. The time now is 04:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com