Macro to insert Blank row and add data

[mystic42]

Hi

I am very new to writing VBA's and am stupped on getting this macro right, I need to do the following:

- When the value in a cell in column C changes compared to the cell value above - a blank line needs to be inserted between the different values

- then I need to copy the values from the cells below the Blank Row Columns A B C J L into the blank cell as well

and add on the blank line "Balance" in Column D

Any help would be greatly appreciated.

Thanks

Andrea

 

[mystic42]

Clarification : I am currently using Excel 2003 but am upgrading to 2010 at the end of the month:

[TABLE="width: 1069"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD] E F G H I[/TD]
[TD] J[/TD]
[TD]K[/TD]
[TD] L[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Header Row[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Header Row[/TD]
[TD][/TD]
[TD]1[/TD]
[TD="colspan: 8"] Header Row
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C2[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD]C2[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]X123[/TD]
[TD]Box[/TD]
[TD]Apples[/TD]
[TD]Balance[/TD]
[TD][/TD]
[TD] 5,000.00[/TD]
[TD][/TD]
[TD]20.12.13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C3[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD]C3[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]X123[/TD]
[TD]Box[/TD]
[TD]Apples[/TD]
[TD]PURCHASE[/TD]
[TD]….[/TD]
[TD] 5,000.00[/TD]
[TD][/TD]
[TD]20.12.13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C4[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD]C4[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]X124[/TD]
[TD]Box[/TD]
[TD]Apples[/TD]
[TD]SALE[/TD]
[TD]….[/TD]
[TD]- 2,000.00[/TD]
[TD][/TD]
[TD]21.12.13[/TD]
[/TR]
[TR]
[TD="align: center"]b[/TD]
[TD]C5[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD]C5[/TD]
[TD]Apples[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]X125[/TD]
[TD]Box[/TD]
[TD]Apples[/TD]
[TD]PURCHASE[/TD]
[TD]….[/TD]
[TD] 400.00[/TD]
[TD][/TD]
[TD]21.12.13[/TD]
[/TR]
[TR]
[TD="align: center"]e[/TD]
[TD]C6[/TD]
[TD]Apples[/TD]
[TD]a[/TD]
[TD]C6[/TD]
[TD]Apples[/TD]
[TD="align: center"]R[/TD]
[TD]6[/TD]
[TD]X126[/TD]
[TD]Box[/TD]
[TD]Apples[/TD]
[TD]PURCHASE[/TD]
[TD]….[/TD]
[TD] 4.00[/TD]
[TD][/TD]
[TD]04.01.14[/TD]
[/TR]
[TR]
[TD="align: center"]f[/TD]
[TD]C7[/TD]
[TD]Oranges[/TD]
[TD]f[/TD]
[TD]C7[/TD]
[TD]Apples[/TD]
[TD="align: center"]e[/TD]
[TD]7[/TD]
[TD]X127[/TD]
[TD]Box[/TD]
[TD]Apples[/TD]
[TD]SALE[/TD]
[TD]….[/TD]
[TD]- 500.00[/TD]
[TD][/TD]
[TD]05.01.14[/TD]
[/TR]
[TR]
[TD="align: center"]o[/TD]
[TD]C8[/TD]
[TD]Oranges[/TD]
[TD]t[/TD]
[TD]C8[/TD]
[TD][/TD]
[TD="align: center"]s[/TD]
[TD]8[/TD]
[TD]z22[/TD]
[TD]Cart[/TD]
[TD]Oranges[/TD]
[TD]Balance[/TD]
[TD][/TD]
[TD] 550.00[/TD]
[TD][/TD]
[TD]13.12.13[/TD]
[/TR]
[TR]
[TD="align: center"]r[/TD]
[TD]C9[/TD]
[TD]Pears[/TD]
[TD]e[/TD]
[TD]C9[/TD]
[TD]Oranges[/TD]
[TD="align: center"]u[/TD]
[TD]9[/TD]
[TD]z22[/TD]
[TD]Cart[/TD]
[TD]Oranges[/TD]
[TD]PURCHASE[/TD]
[TD]….[/TD]
[TD] 550.00[/TD]
[TD][/TD]
[TD]13.12.13[/TD]
[/TR]
[TR]
[TD="align: center"]e[/TD]
[TD]C10[/TD]
[TD]Pears[/TD]
[TD]r[/TD]
[TD]C10[/TD]
[TD]Oranges[/TD]
[TD="align: center"]l[/TD]
[TD]10[/TD]
[TD]y35[/TD]
[TD]Box[/TD]
[TD]Oranges[/TD]
[TD]SALE[/TD]
[TD]….[/TD]
[TD]- 500.00[/TD]
[TD][/TD]
[TD]20.12.13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C11[/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD]C11[/TD]
[TD][/TD]
[TD="align: center"]t[/TD]
[TD]11[/TD]
[TD="colspan: 8"] etc..
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C12[/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD]C12[/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C13[/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C14[/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C15[/TD]
[TD]Pears[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hope this explains better what I am trying to do:

Thanks for any help that can be provided.

Regards

Andrea

 

[mirabeau]

Hi

I am very new to writing VBA's and am stupped on getting this macro right, I need to do the following:

- When the value in a cell in column C changes compared to the cell value above - a blank line needs to be inserted between the different values

- then I need to copy the values from the cells below the Blank Row Columns A B C J L into the blank cell as well

and add on the blank line "Balance" in Column D

Any help would be greatly appreciated.

Thanks

Andrea

Hi Andrea,

Maybe try the following VBA code. It should do literally what you ask for in the above quote, but if it's really what you want ... who knows?. Your posts 2 and 3 don't clarify much at all for me.

Sub insert_blank_rows_etcr()

Dim r As Long, c As Long, a, u(), i As Long, k As Long
r = Cells.Find("*", , , , xlByRows, xlPrevious).Row
c = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
ReDim u(1 To r, 1 To 1)
a = Cells(3).Resize(r + 1)
Cells(c + 1) = 1: Cells(c + 1).Resize(r).DataSeries

For i = 1 To r
    If a(i, 1) <> a(i + 1, 1) Then k = k + 1: u(k, 1) = i
Next i

Cells(r + 1, c + 1).Resize(k) = u
Cells(1).Resize(r + k, c + 1).Sort Cells(c + 1), Header:=xlNo
Cells(c + 1).Resize(r + k).ClearContents

For i = 1 To k
    Cells(u(i, 1) + i, 1).Resize(, 3) = Cells(u(i, 1) + i + 1, 1).Resize(, 3).Value
    If i < k Then Cells(u(i, 1) + i, 4) = "Balance"
    Cells(u(i, 1) + i, 10) = Cells(u(i, 1) + i + 1, 10)
    Cells(u(i, 1) + i, 12) = Cells(u(i, 1) + i + 1, 12)
Next i

End Sub

 

[mystic42]

Thanks Mirabeau

Will try out on Monday, thanks again

Andrea

 

[mystic42]

Hello mirabeau

I get a runtime error 1004 and when I select debug the following line in highlighted:

a = Cells(3).Resize(r + 1)


Andrea

 

[mystic42]

Correction - Works perfectly - thank you

Andrea