Solved

How can I combine rows using VBA in excel?

Posted on 2006-10-24
6
214 Views
Last Modified: 2010-05-18
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
Comment
Question by:tiehaze
  • 4
  • 2
6 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 17798754
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17798834
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
 

Author Comment

by:tiehaze
ID: 17799051
Is there anyway to do this without pivot tables? I am not real familiar with them and am having problems...
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17799126
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
 

Author Comment

by:tiehaze
ID: 17799436
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17799524
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now