Code to insert 2 rows and sum column

Topic

New Reply

I have searched the lounge and tried to “piece” together code to accomplish what i need done…no luck. I have a work sheet with individual initials in Column A [all initials in Column A are 3 characters in length and I have all 50 workbooks sorted alphabetically by the first letter of the initials ]. In Column B is an amount spent.

What I need help on is vba or other method to run through column A and the when the initials change {i.e. ABC to ABD, from ABD to ABE, etc.] then insert 2 blank rows. Then sum the numbers in column B for that initial. The process continues until column A is blank [or least I hope that its blank—the initials were pulled from a SQL database–so I am assuming that since I don’t “see” anything in column A, that it is really “blank”.

The worksheets do not have any named ranges, etc. though I could create them. Each worksheet varies in the amount of data in Column A [for example my first worksheet has only 600 rows of data—but several other worksheets have more than 2,500 rows of data.

Can this process be coded or otherwise mechanized? THX.

Reply | Quote

Replies

Why not use subtotals? You won’t get an extra blank row between the groups, but it’s hardly any work. You can automate creating the subtotals:

Code:

Sub CreateSubtotals()
  Dim wsh As Worksheet
  For Each wsh In ActiveWorkbook.Worksheets
	wsh.UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
	 Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  Next wsh
End Sub

Reply | Quote

Why not use subtotals? You won’t get an extra blank row between the groups, but it’s hardly any work. You can automate creating the subtotals:

Code:

Sub CreateSubtotals()
  Dim wsh As Worksheet
  For Each wsh In ActiveWorkbook.Worksheets
[tab][/tab]wsh.UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
[tab][/tab] Replace:=True, PageBreaks:=False, SummaryBelowData:=True
  Next wsh
End Sub

Hans,
Wow! No, make that a double Wow…I have never used “sub-totals”…always created these manually.

Is it possible to also insert a blank row after the sub-total? The boss wants the “break” the data between individiuals with at least one blank line after the sub-total. Thx.
JimC

Reply | Quote

Is it possible to also insert a blank row after the sub-total? The boss wants the “break” the data between individiuals with at least one blank line after the sub-total.

The subtotals as provided by Excel are more or less automatic. To insert blank rows would be a lot more work.

Reply | Quote

The subtotals as provided by Excel are more or less automatic. To insert blank rows would be a lot more work.

Hans,
Thanks for the advice…
JimC

Reply | Quote

Try this variation of HanV’s macro:

[codebox]Sub CreateSubtotals()
Dim wsh As Worksheet
Dim i As Long, lRow As Long
For Each wsh In ActiveWorkbook.Worksheets
wsh.UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

lRow = wsh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lRow To 1 Step -1
If InStr(wsh.Cells(i, 1), “Total”) > 0 Then
wsh.Cells(i + 1, 1).EntireRow.Insert
End If
Next
Next wsh
End Sub[/codebox]

Reply | Quote

Try this variation of HanV’s macro:

[codebox]Sub CreateSubtotals()
Dim wsh As Worksheet
Dim i As Long, lRow As Long
For Each wsh In ActiveWorkbook.Worksheets
wsh.UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

lRow = wsh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lRow To 1 Step -1
If InStr(wsh.Cells(i, 1), “Total”) > 0 Then
wsh.Cells(i + 1, 1).EntireRow.Insert
End If
Next
Next wsh
End Sub[/codebox]

Mike,
Thanks..Maybe I am doing something wrong, but your code places a blank row every row and it does not produce sub-totals by initials. I copied your code just like I did for Hans code to avoid typos, etc. Is there something I am missing? A blank row would make the boss–happy–at least temporarily anyway. Thx.
JimC

Reply | Quote

Would it be possible to post a sample book?

I don’t know if version matters – I’m using 2003. If you have 2007, please post a 2003 or earlier version of the file.

I’ve attached a zip with before and after wokbooks.

Reply | Quote

Would it be possible to post a sample book?

I don’t know if version matters – I’m using 2003. If you have 2007, please post a 2003 or earlier version of the file.

I’ve attached a zip with before and after wokbooks.

Mike,
Let me look at your example further…Sorry for not providing all relevant information–I am using Excel 2002 [xp]. THANKS.
JimC.

Reply | Quote

Would it be possible to post a sample book?

I don’t know if version matters – I’m using 2003. If you have 2007, please post a 2003 or earlier version of the file.

I’ve attached a zip with before and after wokbooks.

Mike,
Thanks…I got it to work…somehow between working back and forth between the lounge and my excel file..a hidden column was in my worksheet…once I discovered it and deleted the column your code worked great…I checked some of my other worksheets and they worked on the first try. Again, thank you so much…I also learned something new along the way that I can re-use again.
Take care.
JimC

Reply | Quote

Would it be possible to post a sample book?

I don’t know if version matters – I’m using 2003. If you have 2007, please post a 2003 or earlier version of the file.

I’ve attached a zip with before and after wokbooks.

Mike,
I have a follow-up question and another favor to ask. I am trying to “learn” as I go through this process, so here is my question.

My first 20+ worksheets were just like your example with no blank rows at the top of the worksheet. My column headings –salesperson and amount were in row 1 columns A and B. But then I had several worksheets that had 2-3 blank rows and the column heading was in row 3 or 4 [i.e. these worksheets were prepared by a customer from some sort of database extract process]. When I ran your code on the worksheets with the blank rows, I rec’d a message box, “Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command”. I clicked “OK” and it still runs, but I have a sub-total in the blank row—which I delete.

So my question in reviewing your code, what causes the assumption regarding the location of column headings? I know very little about VBA, but it would appear to me that its not your code, but an assumption of the sub-total function itself? Is this correct?

Now my favor…The boss would like me to create a separate worksheet page with rows that contain only the sub-totals. So my sheet2 would have the same two columns, but only list initials in column A and sub-total amount in column B. Is it possible to modify your code to do this or should this be another macro that “reads” column A looking for the initial-total text to snag the amount in column B? I have so little knowledge of vba, I really can’t determine if this task can be incorporated into the current macro or is too different and would needs it own separate macro?

Thanks again for all of your help… JimC

Reply | Quote

You could create pivot tables to display only the subtotals. No code needed.

Reply | Quote

You could create pivot tables to display only the subtotals. No code needed.

Hans,
Thanks…I have never used a pivot table…I guess its time for another learning experience.
JimC

Reply | Quote