VBA Excel consolidate rows and then move results to another column

[Pointman92]

Sub CombineRows()
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "Ryan's Code"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub


This is my code. I want the results to go into column N+O.

I do not know how to do that in VBA and tried to do it, but the results do not come out correctly.

Thank you for any suggestions/answers

 

[My Aswer Is This]

Please explain in words what your wanting to do.

 

[Pointman92]

I have 2 rows of data many of them repeat in one column. I have another row with numbers.

I want the first row consolidated with no repeats and the other column SUMed to correspond to what was in column 1.
I.E.
Row 1 Row 2
Med 100
Med 100
Med 100
Load 100
Load 100
Load 100
Act 100

to

Row 3 Row 4
Med 300
Load 300
Act 100

I just want the results of the VBA code in different columns and to not touch the original data.

Sorry about the confusion and thank you!

 

[sheetspread]

The pivot table does this

 

[My Aswer Is This]

I think your confusing rows and columns:
You said:
I have 2 rows of data many of them repeat in one column

Do you mean you have 2 columns of data

You said:
I have another row with numbers.


Do you mean you have another Column with numbers?


I think you need to rewrite your question.

 

[Pointman92]

Yes, but I want it in VBA so that I can make the code into a button and as new data comes in one can click a button and it'll automatically consolidate.
I have a connection to a sql server and it will automatically update a table when new data comes in.

Thank you!

 

[sheetspread]

You can connect the pivot table to sql server and hit the refresh button each time without VBA. Or record a macro doing so if this must be in code.

 

[Pointman92]

I have 2 columns of data

I want 1 column consolidated and the other column sumed

depending on what the first column was.

Then I want the results in 2 different columns.

To my understanding a column goes up and down
A row goes left to right

I am talking about colomns

 

[My Aswer Is This]

I'm surprised in post #1 you said:
This is my code.

But in post # 8 you seemed to indicate your not sure about what a row and a column is.
Have you ever installed and run a Vba macro?

 

[tonyyy]

Pointman92,

You might give the following modification a try...

Sub CombineRows()
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant

On Error Resume Next
xTitleId = "Ryan's Code"
Set WorkRng = Application.InputBox("Range", xTitleId, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value

For i = 1 To UBound(arr, 1)
    Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next

Application.ScreenUpdating = False
Range("N1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
Range("O1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub

Cheers,

tonyyy