1

Problem: This is for Excel 2010

It is rarely that I need to merge the result of multiple rows if they are have a common value (In this case is PN#), while there are a few column similar but not the same (description and Name), while the quantity at different columns are automatically summed. However, it is nice to have such ability.

This can be done using SUMIF multiple time Explain expected result:

enter image description here

Available resource: How to SUM / merge similar rows in Excel using VBA?

When the macro run, I would like to: 1. Specified the range, include the header 2. Choose the main column that uses to compare the name (PN#) 3. Choose the columns that will not be summed but just simple choose the first occurrence (Description and Name) 4. The other columns will be summed 5. Choose where I Can put the result table

What have I tried/done:

  1. I am able to get the range from selection. But wish I could do something like Excel has to select the range

enter image description here

  1. Inputboxes to let user choose which column to be used to compare, which columns to choose the first occurrence of PN#, and which columns to be summed.

My main question is to how to merge the rows?

Improve this question
1
  • +1 for a well researched and formatted question. Next time however it might be useful to also add the Version of Excel you're using. Commented May 6, 2012 at 0:42

2 Answers 2

Reset to default
1

As far as I am aware, the built-in range dialog cannot be used. However, you can use an InputBox to ask for a range, e.g.

dim rRange as Range
Set rRange = Application.InputBox("Please choose a range", "Specify Range", Type:=8)

To caption your label afterwards, you can use something like:

lblYourLabel.Caption = rRange.Address

You may want to add some error handling to it and consult the manual on InputBox, but this should get you started.

Hope that helps, Stefan

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

This is not a important step but I want to display the selected range to a label. How can I do it. I tried but got type mismatch
You could print the .RefersTo property, for example. What else have you tried? Where do you get the type mismatch?
When I try to bind the rRange to a label. For example range = A1:C100, I just want to display label1.text = "A1:C100".
Have you tried the suggestion? E.g. label1.Text = rRange.RefersTo?
My mistake: Label1.Caption = rRange.Address. I was thinking about named ranges. However, you could have found that answer in the manual...
|
1

If you design your functionality around a user form , there is a control you can add that does this: RefEdit.Ctrl
It allows the user to select a range and returns that range address.

Improve this answer

5 Comments

is there any way to bind the selected range header to combo box?
You might want to start a new question for that - it does not make any sense to "exploit" the answers here for new topics. Also, you should accept an answer if it fixed the problem in your question. Thanks.
Thanks but the main question has not been answered. I have not been able to merge the rows.
That was not the question, though. You can "bind" a ComboBox to a range by using its ListFillRange property. cmbBox.ListFillRange = rRange.RefersTo (or you might need to use the RefersToRange Property instead, can't remember)
Sorry my question might not be clear:the question is how to create the code that can merge the rows based on provided criteria: There is a column to compare if there are same items, columns that will use to merge the information (just pick whichever appears first), then sum the total of other columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.