Inserting Rows ....

[excel_newbie_2]

Hello everyone

I was wondering if you could help me.. I am a beginner and need some help with the code I have just written. I need to alter this code in order to do 2 things

1. to leave a blank line inbwteen Shop A's Apples, Shop A's Pears etc
2. I also wanted to total up the price totals....(see example below).So if the last price was in E4, the total of all those prices above would show in F5

SHOP A STAT 1 STAT 2 APPLES 2.30
SHOP A STAT 1 STAT 2 APPLES 2.30
4.60
SHOP A STAT 1 STAT 2 PEARS 3.40
SHOP A STAT 1 STAT 2 PEARS 4.10
7.50


This is the code I need to alter for the blank lines and totals (i've only included the code for SHOP A)-

Sub Macro1()
Application.ScreenUpdating = False
Sheets("Data").Select
Lastrow = Range("A65536").End(xlUp).Row

For i = 1 To Lastrow
Sheets("Data").Select

If Cells(i, 1) = "SHOP A" _
And Cells(i, 4) = "APPLES" Then
Rows(i & ":" & i).Select
Selection.Copy
Sheets("Sheet3").Select
PasteRow = Range("F65536").End(xlUp).Offset(1, 0).Row
Rows(PasteRow & ":" & PasteRow).Select
Selection.Insert Shift:=xlDown
End If

Next i

Range("A1").Select
Application.ScreenUpdating = True
Application.ScreenUpdating = False
Sheets("Data").Select
Lastrow = Range("A65536").End(xlUp).Row

For i = 1 To Lastrow
Sheets("Data").Select

If Cells(i, 1) = "SHOP A" _
And Cells(i, 4) = "PEARS" Then
Rows(i & ":" & i).Select
Selection.Copy
Sheets("Sheet3").Select
PasteRow = Range("F65536").End(xlUp).Offset(1, 0).Row
Rows(PasteRow & ":" & PasteRow).Select
Selection.Insert Shift:=xlDown
End If

Next i

Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[Domski]

Hi,

I assume you can't just use Excel's Subtotal function?

Dom

 

[excel_newbie_2]

Hi Dom,

Thanks for your post...

You're right - It would probably be easier for me to do that .... (but if anyone knows how to do this please let me know!)

However, the main problem is trying to insert the blank rows .... any idea?

Thanks in advance
Isha

 

[Domski]

Just so I understand correctly...If the shop name in column A or the product name in column D changes you want to insert a row and then a total for that shop/product combination of column F. Correct?

Dom

 

[excel_newbie_2]

Hi Dom

Yes thats absolutely what im trying to do ..

 

[Domski]

Hi,

A couple of subs then:

Sub insert_rows()
Dim lastRow As Long, myRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For myRow = lastRow To 3 Step -1
If Cells(myRow, 1) <> Cells(myRow - 1, 1) Or Cells(myRow, 4) <> Cells(myRow - 1, 4) Then
Rows(myRow).insert
End If
Next myRow
End Sub
 
Sub insert_totals()
Dim lastRow As Long, myRow As Long, startRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
startRow = 2
For myRow = startRow To lastRow
    If Cells(myRow, 1) = "" Then 
 
        Cells(myRow, 5).Formula = "=SUM(E" & startRow & ":E" & myRow - 1 & ")"
        startRow = myRow + 1
 
    End If
 
Next myRow
End Sub

First one puts the blank rows in and the second the totals.

Both codes assume you have headings in Row 1.

Hope it helps,

Dom

 

[excel_newbie_2]

Hi Dom,

Before I test it ---
Do I need to add your 2 subs after every bit of code that returns a shop/fruit combo, as in the exmple below. And if so I am not sure where I should put EndSub for my bits code

Or can I add your 2 subs to the very bottom of my code?

eg (your code is in black, mine is in pink)

Sub Macro1()
Application.ScreenUpdating = False
Sheets("Data").Select
Lastrow = Range("A65536").End(xlUp).Row

For i = 1 To Lastrow
Sheets("Data").Select

If Cells(i, 1) = "SHOP A" _
And Cells(i, 4) = "APPLES" Then
Rows(i & ":" & i).Select
Selection.Copy
Sheets("Sheet3").Select
PasteRow = Range("F65536").End(xlUp).Offset(1, 0).Row
Rows(PasteRow & ":" & PasteRow).Select
Selection.Insert Shift:=xlDown
End If

Next i

Range("A1").Select
Application.ScreenUpdating = True
END SUB???

Sub insert_rows()
Dim lastRow As Long, myRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For myRow = lastRow To 3 Step -1
If Cells(myRow, 1) <> Cells(myRow - 1, 1) Or Cells(myRow, 4) <> Cells(myRow - 1, 4) Then
Rows(myRow).insert
End If
Next myRow
End Sub

Sub insert_totals()
Dim lastRow As Long, myRow As Long, startRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
startRow = 2
For myRow = startRow To lastRow
If Cells(myRow, 1) = "" Then

Cells(myRow, 5).Formula = "=SUM(E" & startRow & ":E" & myRow - 1 & ")" startRow = myRow + 1

End If

Next myRow
End Sub

Application.ScreenUpdating = False
Sheets("Data").Select
Lastrow = Range("A65536").End(xlUp).Row

For i = 1 To Lastrow
Sheets("Data").Select

If Cells(i, 1) = "SHOP A" _
And Cells(i, 4) = "PEARS" Then
Rows(i & ":" & i).Select
Selection.Copy
Sheets("Sheet3").Select
PasteRow = Range("F65536").End(xlUp).Offset(1, 0).Row
Rows(PasteRow & ":" & PasteRow).Select
Selection.Insert Shift:=xlDown
End If

Next i

Range("A1").Select
Application.ScreenUpdating = True
END SUB??

Sub insert_rows()
Dim lastRow As Long, myRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For myRow = lastRow To 3 Step -1
If Cells(myRow, 1) <> Cells(myRow - 1, 1) Or Cells(myRow, 4) <> Cells(myRow - 1, 4) Then
Rows(myRow).insert
End If
Next myRow
End Sub

Sub insert_totals()
Dim lastRow As Long, myRow As Long, startRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
startRow = 2
For myRow = startRow To lastRow
If Cells(myRow, 1) = "" Then

Cells(myRow, 5).Formula = "=SUM(E" & startRow & ":E" & myRow - 1 & ")"
startRow = myRow + 1

End If

Next myRow
End Sub


As always, really appreciate your time and effort

 

[Domski]

Not quite, I think the subs that I have posted should do what you want without any other code.

This assumes you start with a contiguous block of data in columns A:F starting with headers in row 1 and data from row 2 downwards.

The first sub loops through inserting a blank row whenever the Shop or Product changes and then the second loops through again adding the totals.

It could be done with one sub but thought it might be clearer to see what was going on with two.

Dom

 

[excel_newbie_2]

Thanks Dom, works perfectly!!! Its amazing.

I'm going to try to get my head round the code.

If I wanted to insert 2 rows instead of 1, how would I alter the insert row sub?? And would I then have to alter the total sub as there would be 2 blank rows instead of one ?

Thanks so much you've been so much help

 

[Domski]

No worries, on my mobile now but I'll have a look at popping some comments in the code later to explain what it's doing and show how to add the extra row in.