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.

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.

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.

Macro to insert rows in Excel
 

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.

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.

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.

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.

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.

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.