Macro to insert rows in Excel

[kden]

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.

[gocush]

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.
>

[schandran]

Sub insert()
Do While ActiveCell.Value <> 0
If ActiveCell.Value <> ActiveCell.Offset(1).Value Then
ActiveCell.Offset(1, 0).Select
For i = 1 To 3
Selection.EntireRow.Insert
Next i
ActiveCell.Offset(3, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

[kden]

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.
> >

[Don Guillett]

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:b2000)

--
Don Guillett
SalesAid Software
[email protected]
"kden" <[email protected]> wrote in message
news:[email protected]...
> 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.
> > >

[gocush]

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: Data>Subtotals 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.
> > >

[kden]

Thank you for the suggestion, but I need to keep everything on one worksheet
because there's multiple worksheets in the workbook. Additionally, this is
just one macro in many that are used to reconcile the accounts, so data needs
to be on relavent worksheets.

However, I'm sure your suggestion will come in handy for another "challenge"
down the road. With much appreciation, thank you.

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:b2000)
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "kden" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> > > >
>
>
>

[kden]

Thank you for this suggestion. I'll try this and will let you know the
outcome.

Thank you also for your concern about adding new transactions. Good thing
is, information is static so no additional info/changes to be made to the
worksheet. If there are, new data is exported and I run my series of macros.

Again, will get back to you to let you know the outcome.

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: Data>Subtotals 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.
> > > >

[kden]

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: Data>Subtotals 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.
> > > >

[kden]

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:b2000)
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "kden" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> > > >
>
>
>

[kden]

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: Data>Subtotals 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.
> > > >

[Josh O.]

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: Data>Subtotals 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.
> > > > >