Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert rows in Excel
I'm trying to create a macro which will search the data (text and/or number)
in column A. When it finds a new number, I would like to insert 2-3 rows at the end of the previous number, then continue through the entire worksheet doing the same thing. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert rows in Excel
Can you tell us what you mean by a "New number"
"kden" wrote: I'm trying to create a macro which will search the data (text and/or number) in column A. When it finds a new number, I would like to insert 2-3 rows at the end of the previous number, then continue through the entire worksheet doing the same thing. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert rows in Excel
Column A is account numbers, with each row an individual transaction. So,
there may be thousands of transactions for a single account number. The macro would search the numbers (in column A) and when there's a "new number" (essentially, new transactions for a different account number), I'd like for the macro to insert 2-3 rows at the end of each account so that I can sum the debits/credits for the account. "gocush" wrote: Can you tell us what you mean by a "New number" "kden" wrote: I'm trying to create a macro which will search the data (text and/or number) in column A. When it finds a new number, I would like to insert 2-3 rows at the end of the previous number, then continue through the entire worksheet doing the same thing. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert rows in Excel
From this description, I assume that you have sorted the list of transactions
on Col A. (All transactions for Acnt "A" are together, followed by all transactions for Acnt "AB" etc) If you just want to total the transactions for each acnt you should be able to do this with the SUBTOTAL function: DataSubtotals See Help for instructions If you want to insert blank rows and create you own subtotal try the following. The downside of this is that the blank rows are then hardcoded and adding new transactions to you database might be difficult. Option Explicit Sub InsertRows() Sub InsertRows() Dim oCell As Range Dim lTransCount As Long Dim L As Long lTransCount = Application.CountA(Columns(1)) For L = lTransCount To 2 Step -1 If Range("A" & L) < Range("A" & L).Offset(-1, 0) Then MsgBox Range("A" & L).Offset(-1, 0).Address & " is the last row for an acnt" 'Insert 3 blank rows Range(Range("A" & L), Range("A" & L).Offset(2, 0)).Insert xlShiftDown End If Next L End Sub "kden" wrote: Column A is account numbers, with each row an individual transaction. So, there may be thousands of transactions for a single account number. The macro would search the numbers (in column A) and when there's a "new number" (essentially, new transactions for a different account number), I'd like for the macro to insert 2-3 rows at the end of each account so that I can sum the debits/credits for the account. "gocush" wrote: Can you tell us what you mean by a "New number" "kden" wrote: I'm trying to create a macro which will search the data (text and/or number) in column A. When it finds a new number, I would like to insert 2-3 rows at the end of the previous number, then continue through the entire worksheet doing the same thing. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert rows in Excel
Tried the macro as you outlined below, but it didn't work as I was asking.
Instead of inserting 3 rows, it inserted 3 spaces (before the new number) in column A and pushed all the information down accordingly, just in column A. Also, a message box kept popping up, which, if possible, I'd like to eliminate. Keeping my fingers crossed... kden "gocush" wrote: From this description, I assume that you have sorted the list of transactions on Col A. (All transactions for Acnt "A" are together, followed by all transactions for Acnt "AB" etc) If you just want to total the transactions for each acnt you should be able to do this with the SUBTOTAL function: DataSubtotals See Help for instructions If you want to insert blank rows and create you own subtotal try the following. The downside of this is that the blank rows are then hardcoded and adding new transactions to you database might be difficult. Option Explicit Sub InsertRows() Sub InsertRows() Dim oCell As Range Dim lTransCount As Long Dim L As Long lTransCount = Application.CountA(Columns(1)) For L = lTransCount To 2 Step -1 If Range("A" & L) < Range("A" & L).Offset(-1, 0) Then MsgBox Range("A" & L).Offset(-1, 0).Address & " is the last row for an acnt" 'Insert 3 blank rows Range(Range("A" & L), Range("A" & L).Offset(2, 0)).Insert xlShiftDown End If Next L End Sub "kden" wrote: Column A is account numbers, with each row an individual transaction. So, there may be thousands of transactions for a single account number. The macro would search the numbers (in column A) and when there's a "new number" (essentially, new transactions for a different account number), I'd like for the macro to insert 2-3 rows at the end of each account so that I can sum the debits/credits for the account. "gocush" wrote: Can you tell us what you mean by a "New number" "kden" wrote: I'm trying to create a macro which will search the data (text and/or number) in column A. When it finds a new number, I would like to insert 2-3 rows at the end of the previous number, then continue through the entire worksheet doing the same thing. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert rows in Excel
Looking at this further, this would not help me with what I'm trying to do.
Looks like this is just giving me one total sum, whereas I'm trying to get totals for EACH set of numbers. Still trying to figure-out my original problem. kden "Don Guillett" wrote: I would suggest you leave your database alone and use another method on another sheet to get the sums something like: =sumproduct((sheet2!a2:a2000=12345)*sheet2!b2:b200 0) -- Don Guillett SalesAid Software "kden" wrote in message ... Column A is account numbers, with each row an individual transaction. So, there may be thousands of transactions for a single account number. The macro would search the numbers (in column A) and when there's a "new number" (essentially, new transactions for a different account number), I'd like for the macro to insert 2-3 rows at the end of each account so that I can sum the debits/credits for the account. "gocush" wrote: Can you tell us what you mean by a "New number" "kden" wrote: I'm trying to create a macro which will search the data (text and/or number) in column A. When it finds a new number, I would like to insert 2-3 rows at the end of the previous number, then continue through the entire worksheet doing the same thing. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert rows in Excel
gocush:
By any chance, did you happen to see/get my comment on this when I tried it? I'm still trying to figure-out how to complete this task. kden "gocush" wrote: From this description, I assume that you have sorted the list of transactions on Col A. (All transactions for Acnt "A" are together, followed by all transactions for Acnt "AB" etc) If you just want to total the transactions for each acnt you should be able to do this with the SUBTOTAL function: DataSubtotals See Help for instructions If you want to insert blank rows and create you own subtotal try the following. The downside of this is that the blank rows are then hardcoded and adding new transactions to you database might be difficult. Option Explicit Sub InsertRows() Sub InsertRows() Dim oCell As Range Dim lTransCount As Long Dim L As Long lTransCount = Application.CountA(Columns(1)) For L = lTransCount To 2 Step -1 If Range("A" & L) < Range("A" & L).Offset(-1, 0) Then MsgBox Range("A" & L).Offset(-1, 0).Address & " is the last row for an acnt" 'Insert 3 blank rows Range(Range("A" & L), Range("A" & L).Offset(2, 0)).Insert xlShiftDown End If Next L End Sub "kden" wrote: Column A is account numbers, with each row an individual transaction. So, there may be thousands of transactions for a single account number. The macro would search the numbers (in column A) and when there's a "new number" (essentially, new transactions for a different account number), I'd like for the macro to insert 2-3 rows at the end of each account so that I can sum the debits/credits for the account. "gocush" wrote: Can you tell us what you mean by a "New number" "kden" wrote: I'm trying to create a macro which will search the data (text and/or number) in column A. When it finds a new number, I would like to insert 2-3 rows at the end of the previous number, then continue through the entire worksheet doing the same thing. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert rows in Excel
Don't know if you ever got an answer to your question, but try this. It will
insert 1 row after each block of numbers (assuming you have it sorted according to account number). You can play with it to get it to insert more rows. Sub Add Rows Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i = lastrow To 2 Step -1 If Cells(i, 1) < Cells(i - 1, 1) Then Rows(i).Insert End If Next End Sub "kden" wrote: gocush: By any chance, did you happen to see/get my comment on this when I tried it? I'm still trying to figure-out how to complete this task. kden "gocush" wrote: From this description, I assume that you have sorted the list of transactions on Col A. (All transactions for Acnt "A" are together, followed by all transactions for Acnt "AB" etc) If you just want to total the transactions for each acnt you should be able to do this with the SUBTOTAL function: DataSubtotals See Help for instructions If you want to insert blank rows and create you own subtotal try the following. The downside of this is that the blank rows are then hardcoded and adding new transactions to you database might be difficult. Option Explicit Sub InsertRows() Sub InsertRows() Dim oCell As Range Dim lTransCount As Long Dim L As Long lTransCount = Application.CountA(Columns(1)) For L = lTransCount To 2 Step -1 If Range("A" & L) < Range("A" & L).Offset(-1, 0) Then MsgBox Range("A" & L).Offset(-1, 0).Address & " is the last row for an acnt" 'Insert 3 blank rows Range(Range("A" & L), Range("A" & L).Offset(2, 0)).Insert xlShiftDown End If Next L End Sub "kden" wrote: Column A is account numbers, with each row an individual transaction. So, there may be thousands of transactions for a single account number. The macro would search the numbers (in column A) and when there's a "new number" (essentially, new transactions for a different account number), I'd like for the macro to insert 2-3 rows at the end of each account so that I can sum the debits/credits for the account. "gocush" wrote: Can you tell us what you mean by a "New number" "kden" wrote: I'm trying to create a macro which will search the data (text and/or number) in column A. When it finds a new number, I would like to insert 2-3 rows at the end of the previous number, then continue through the entire worksheet doing the same thing. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A Macro to Insert Rows | Excel Discussion (Misc queries) | |||
Insert rows macro. | Excel Discussion (Misc queries) | |||
Excel Macro to insert rows | Excel Discussion (Misc queries) | |||
insert rows in excel spreadsheet via macro | Excel Worksheet Functions | |||
Excel Macro to insert rows in a list | Excel Worksheet Functions |