• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

How can I combine rows using VBA in excel?

Here is an example of what I am trying to do:
      
   Column A                               Column B                                      Column C
     574,063                               5.500%, due 01/01/35                               583,254                        
      69,477                                5.500%, due 02/01/35 <=                           72,255
    396,247                                5.500%, due 02/01/35 <=                          420,001    
      65,141                                5.500%, due 02/01/35 <=                           68,000                        
      41,335                                5.500%, due 04/01/35                                 38,254                        
    487,419                                5.500%, due 05/01/35 <=                           488,521                        
  1,090,330                                5.500%, due 05/01/35 <=                       1,362,024
    207,084                                 5.500%, due 06/01/35                               200,585
                                            
Above you see an example with multiple government securities listed with their principal in the column A, the security's rate and maturity in column B, and then the market value in column C.

Can anyone explain how I might be able to write a macro that combines the same securities (the example has arrows pointing to them) and also combines both the principal and market value. For instance, if I were to combine the government security "5.500%, due 02/01/35", the principal value would be 530,865 (69,477 + 396,247 + 65,141) and then the same with the market value.

Anyones help would be extremely appreciated!

Thanks
0
tiehaze
Asked:
tiehaze
  • 4
  • 2
1 Solution
 
Patrick MatthewsCommented:
Hi tiehaze,

I would definitely use a PivotTable.  To do it outside VBA:

1) Make sure the first row of your data set has headers
2) Select Data|PivotTable and PivotChart Report from the menu
3) On step 3 of the wizard, click Layout.  Drag "Terms" (i.e., Col B) into the ROW area, and "Principal" and
"Mkt Value" into the DATA area, making sure both are summarized by sum
4) After finishing the wizard, select the gray cell marked Data, drag it rightward, and "drop" it over the cell
marked total.  That will force the results to array horizontally rather than vertically

Regards,

Patrick
0
 
Patrick MatthewsCommented:
tiehaze,

To do it in VBA, assuming the headings Principal, Terms, and Mkt Value:

Sub MakePT()

    ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'" & ActiveSheet.Name & "'!" & ActiveSheet.UsedRange.Address).CreatePivotTable _
        TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    With ActiveSheet
        .PivotTableWizard TableDestination:=.Cells(3, 1)
        .Cells(3, 1).Select
        With .PivotTables("PivotTable1")
            .AddFields RowFields:=Array("Terms", "Data")
            With .PivotFields("Principal")
                .Orientation = xlDataField
                .Position = 1
            End With
            .PivotFields("Mkt Value").Orientation = xlDataField
            With .DataPivotField
                .Orientation = xlColumnField
                .Position = 1
            End With
        End With
    End With
   
End Sub

Regards,

Patrick
0
 
tiehazeAuthor Commented:
Is there anyway to do this without pivot tables? I am not real familiar with them and am having problems...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Patrick MatthewsCommented:
tiehaze,
> Is there anyway to do this without pivot tables?

Yes, but IMHO the alternatives are clunky and inferior.  Then again, I happen to think PivotTables are the greatest
thing since sliced bread.

> I am not real familiar with them and am having problems...

Such as...?

Regards,

Patrick
0
 
tiehazeAuthor Commented:
Well, the biggest problem is that the table isn't as basic as the example I show. The table is actually approximately 25 columns wide. The error that is showing is the following:

Run-time error '1004':

The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field

Here are a couple of things that might be affecting it, but I am not sure how I would even correct it.

1. There are hidden columns. Do they need to all be unhidden to run it?

2. Not all columns have headings. Do they need to all have headings?

3. Columns A, B, C from the example are not all next to each other. They are actually in columns E, G, and P respectively.

4. There are sections within the spreadsheet. For example, in row 27, there is a totals section. Row 29 starts a new section, which goes all the way down to row 85, in which there is another totals row.  There are approximately 8 sections. Does this affect it?

Thanks for all of your help!
0
 
Patrick MatthewsCommented:
tiehaze,

> Here are a couple of things that might be affecting it, but I am not sure how I
> would even correct it.

> 1. There are hidden columns. Do they need to all be unhidden to run it?

Probably not.

> 2. Not all columns have headings. Do they need to all have headings?

It will work sometimes, but to be safe, yes, we'd want headings on all of 'em.

> 3. Columns A, B, C from the example are not all next to each other. They are actually
> in columns E, G, and P respectively.

Not necessarily a problem, but it would be a big problem if there were blank columns interspersed in the data set.

> 4. There are sections within the spreadsheet. For example, in row 27, there is a
> totals section. Row 29 starts a new section, which goes all the way down to row
> 85, in which there is another totals row.  There are approximately 8 sections. Does
> this affect it?

If you have blank rows mixed in, that's a problem.

It would have been good for you to mention all of this up front, as these details could affect non-PivotTable
alternatives as well.

It would be good for you to upload a sample file to an accessible web site.  If you do not have your own space,
you can use www.ee-stuff.com.

Regards,

Patrick
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now