Inserting Row and Sum Columns VBA

TheWildAfrican

Board Regular
Joined
Apr 23, 2013
Messages
70
Dear All:

I'm trying to write a VBA code that would "Insert Rows" to separate cells with different names and "Sum" the contents of the "Separated" cells.

The file crashes each time I tried to run it.

Please the codes below. I hope you could point out what I've been doing wrong.

Thank you for your anticipated help.

Sub Sum_And_InsertRows()

Dim sum_start As Integer
Dim cnt_add_rows As Integer
Dim i As Integer
Dim fac_name As String

'initialize variable
sum_start = 0
cnt_add_rows = 0

Range("E3").Select

i = 0

While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
fac_name = ActiveCell.Offset(i, 0).Value
sum_start = i

While (ActiveCell.Offset(i, 0).Value = fac_name)

'Separating Deal names and inserting rows
If (ActiveCell.Offset(i - 1, -2).Value <> ActiveCell.Offset(i, -2).Value) Then
ActiveCell.Offset(i + cnt_add_rows, 0).EntireRow.Insert
End If

i = i + 1

Wend

ActiveCell.Offset(i - 1, 7).Value = Application.WorksheetFunction.Sum(Range(ActiveCell.Offset(sum_start, 7), ActiveCell.Offset(i - 1, 7)))

i = i + 1

Wend

End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
From what I see you are selecting E3, if you are inserting rows, you are basically pushing the range down, and the loop will never end.

You need to start at the bottom and work your way up.
I thing you want to loop through column E but see if column C values change?
If not, you should be able to adjust the code to your needs.
VBA Code:
Sub InsertRows()
    Dim sh As Worksheet
    Dim x As Long

    Set sh = ActiveSheet
    With sh
        For x = .Cells(.Rows.Count, "E").End(xlUp).Row To 4 Step -1
            If .Cells(x, "C").Value <> .Cells(x - 1, "C").Value Then
                .Cells(x, "E").EntireRow.Insert
            End If
        Next
    End With
End Sub
 
Upvote 0
Hi Davesexcel,

Many thank you for your response.

I'll try your method and let you know if it works.

I appreciate your help.

PEACE!!

TWA
 
Upvote 0
Hi Davesexcel,

Thanks again for providing a better method to insert rows.

The method works great; however, I could not sum the separated rows using the method.

Would you know the best way to "Sum" the separated rows?

Thanks for your help.

Concrete Respect!!

TWA
 
Upvote 0
Try this

VBA Code:
Sub GetSubs()
    
    Dim RNG As Range, c As Range
    Dim x, y
    Set RNG = Range("G4:G" & Cells(Rows.Count, "G").End(xlUp).Row + 1)
    For Each c In RNG.Cells
        x = 4
        If c = "" Then
            x = y + 2
            y = c.Offset(-1).Row
            c = Application.WorksheetFunction.Sum(Range(Cells(y, "G"), Cells(x, "G")))
            
        End If
        
    Next c
    
End Sub
 
Upvote 0
Hi Davesexcel,

Thank you for all your help and efforts. I've tried using different scenarios with your code, unfortunately I couldn't get it to work.

I truly appreciate your effort, boss.

PEACE!!

TWA
 
Upvote 0
Hi Dave,

Thanks for your response. Most importantly, thanks for all your help.

The inserting rows code works fine. However, I could not get the sum code to work.

I can provide the columns I need to sum are columns L, M, and N.

Please let me know if you need a additional details.

Best,

TWA
 
Upvote 0
Hello Davesexcel and All:

Many thanks for helping on this code.

I'm happy to let you know that I got the code working; albeit "inefficiently"...:):):)

Below is what the updated code.

Thanks to all for your help.

Sub Sum_And_InsertRows()

Dim sum_start As Integer
Dim cnt_add_rows As Integer
Dim i As Integer
Dim fac_name As String

'initialize variable
sum_start = 0
cnt_add_rows = 0

Range("C3").Select ====> I changed this active range as there are many rows contents alike on the previous one I used...

i = 0

While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
fac_name = ActiveCell.Offset(i, 0).Value
sum_start = i

While (ActiveCell.Offset(i, 0).Value = fac_name)

'Separating Deal names and inserting rows
If (ActiveCell.Offset (i , 0).Value <> ActiveCell.Offset (I-1, 0).Value) Then
ActiveCell.Offset (I, 30).value = ActiveCell.Offset (I, 9).value*1 =====> I changed this line from my previous line of "InsertRow.Entire"
End If

i = i + 1

Wend

ActiveCell.Offset (I + cnt_add_row, 0).InsertRow.Entire

i = i + 1

ActiveCell.Offset (i - 1, 9).Value = Application.WorksheetFunction.Sum(Range(ActiveCell.Offset(sum_start, 9), ActiveCell.Offset(i - 1, 9)))

ActiveCell.Offset (I, 0).InsertRow.Entire

i = i + 1

Wend

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top