Solved: Inserting A New Row For Each Account Number In A Spreadsheet

[mattster1010]

Morning,

I have a spreadsheet that contains a list of account numbers in excel that I need to insert a new row below each one to seperate the accounts. The account numbers are contained in Row 'A'. Can anyone give me an exmaple piece of VBA to do this?

Regards,

Mattster

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<<<< change to suit
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = Lastrow to 2 Step -1

.Rows(i + 1).Insert
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[mattster1010]

Thanks xld,

I'd like to sum the total amount of each account (column 'C' holds financial info) in the blank rows inserted below each set of accounts.

Can VBA do this? any pointers would be superb!!

Regards,

Mattster

[Bob Phillips]

Post your workbook and bypass the second-guessing.

[mattster1010]

Hi xld,

Here is the workbook for an example.

Regards,

Mattster

[Bob Phillips]

Just use the built-in Data>Subtotal function, it should give you just what you want.

[mattster1010]

Thanks xld,

Perfect.

Regards,

Mattster